Cannot get multi-line text value from cell

soginiski

New Member
Hello,

So I've been using MS Excel VBO and noticed that when I'm getting a cell value with multiple lines, it returns a single-line string. Any help?

Thanks in advance!
 

sivagelli

Well-Known Member
hi,

In an excel spreadsheet, cell A1, I have added multiple line content of 1258 characters. (Multiple lines are added using Alt + Enter). BP has returned all the characters added to the cell A1 in to a Data Item when I used Get Cell Value action in Excel VBO.

I am not sure if you are asking something different. Please share screenshots if possible.
 

VJR

Well-Known Member
Hi soginiski,

That's true. As mentioned by sivagelli the Get Cell Value returns even if the cell contents have a multi line text in it.
Make sure to open the data item and click on the small button to open up the entire text.
 

soginiski

New Member
Hello sivagelli,

Get Cell Value is what I used first but didn't work. I cannot share screenshots because the data is confidential, but I'll provide an example here.

Let's say the cell contains:
Multiline1
Multiline2
Multiline3

It returns the cell value as this:
Multiline1Multiline2Multiline3
 

sivagelli

Well-Known Member
Ah! i see it now. Yes, the data appears to be in single line (in default Plain Text mode) value if you write to a data item. However, if you try pasting the value to another cell, the value will be multi line.

Also, if you change the Mode in the Multiline Edit pop-up, from Plain Text to any other option from drop-down, you can view in multi-line.
 
Last edited:

VJR

Well-Known Member
Hi soginiski,

Be noted that even if the text in my cell looks to be on multiple lines, it is actually one single line in the top Formula Bar.

View attachment 1542115803705.png

This is because I have enabled 'Wrap Text' for cell A1 in the Format cells and have decreased the width of Column A such that it shows up on three different lines.
So check if that's the case with you.


Now I have removed the Wrap Text and written the text in Cell A1 on three different lines using an Alt Enter for the new line, in which case it shows up exactly the same in the Formula Bar too.

View attachment 1542116028241.png

The text in the Formula Bar could be the one that ends up in the 'Get cell value' return data item.
 

sivagelli

Well-Known Member
hi,

As i stated in my post above, the Value though appears to be a single line (when stored in to a data stage), it is multi line.
You can try to use Split Lines Action from Utility - Strings VBO and can see the output Collection showing multiple lines; in case you are interested in using the values.

Let us know the next step(s) in your flow after reading the cell value.
 

VJR

Well-Known Member
Hi VJR,

The second scenario you showed was the situation I'm having trouble with now.
What sivagelli is trying to suggest is correct. Even though it appears in a straight line in the data item it actually has a new line character between them as seen in a hex editor which is same as your input text in your initial posts.

Below text taken from the returned data item of Get Cell Value.
View attachment 1542175938313.png
 

Shweta

Active Member
Hi soginiski,

Be noted that even if the text in my cell looks to be on multiple lines, it is actually one single line in the top Formula Bar.

View attachment 2506

This is because I have enabled 'Wrap Text' for cell A1 in the Format cells and have decreased the width of Column A such that it shows up on three different lines.
So check if that's the case with you.


Now I have removed the Wrap Text and written the text in Cell A1 on three different lines using an Alt Enter for the new line, in which case it shows up exactly the same in the Formula Bar too.

View attachment 2507

The text in the Formula Bar could be the one that ends up in the 'Get cell value' return data item.
How can we un-wrap the text in excel in Blueprism?
 
Top