Split multiline excel cell data

[KT]

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
------------------------------

[John__Carter]

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
------------------------------

[KT]

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]

@KT

Did you try the following?

Chr(13) & Chr(10)

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

[KT]

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++

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

[MichealCharron]

@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++.

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. 

The above action gives you something like this:

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

[Anton__Hosang]

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

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

[KT]

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

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

[John__Carter]

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
------------------------------