cancel
Showing results for 
Search instead for 
Did you mean: 

Split multiline excel cell data

KT
Level 4

Hi,

I have this excel cell data that is formatted below. I managed to get it inside a collection and was trying to use the Split utility function to split it. Used several delimiters already like \n, newline(), enter, chr(10) but i was not able to split it. Any other ideas?

Sample data inside a single cell

GroupRule1
GroupRule2



------------------------------
K T
------------------------------
8 REPLIES 8

John__Carter
Staff
Staff

You need to find out which characters are at the end of the line, and then do a replace. If you paste the text into a text editor like Notepad++ you might be able to see them. Maybe try CR and LF.



------------------------------
John Carter
Blue Prism
------------------------------

You are right. I can see CRLF in notepad++. I am trying this approach as delimiter "\r\n" or even tried to replaced it but cannot make it to work. how does BP see CRLF?



------------------------------
K T
------------------------------

MichealCharron
Level 8

@KT

Did you try the following?

Chr(13) & Chr(10)


------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------
Micheal Charron
RBC
Toronto, Ontario
Canada

I tried to use it this way "Chr(13) & Chr(10)" on both a Replace function and the Split Text utility. Both not working.

Below is how i see it on notepad++

30632.png



------------------------------
K T
------------------------------

@KT

That is weird that it is not working for you. I tried your example text and it worked fine, for me, with the "Split Text" action. This is how my text looks in Notepad++.

30633.png
If the text is always going to be rows of "GroupRule" with a number, you could always try the "Extract Regex All Matches" action in BP's "Utility - Strings" VBO. 
30634.png
The above action gives you something like this:
30635.png


------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------
Micheal Charron
RBC
Toronto, Ontario
Canada

You've probably solved this already, but the Chr functions are not to be enclosed in text quotes.

Using the Split Text routine in the Strings VBO just put Chr(10) as the parameter

30637.png



------------------------------
Anton Hosang
Director
Omega RPA Ltd
------------------------------

Unfortunately still have not figure it out. not sure why its not detecting that CRLF.



------------------------------
K T
------------------------------

Try substituting another character for CRLF first, eg Replace(Replace([Text from Excel], Chr(10), ""), Chr(13), "#"), and then pass the result to the Split action, using the same split character, eg #.

Obviously the assumption here is that # doe snot already exist in your cell text.



------------------------------
John Carter
Blue Prism
------------------------------