topic RE: Split multiline excel cell data in Product Forum

Split multiline excel cell data

KT — Fri, 19 Jan 2024 02:16:00 GMT

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

RE: Split multiline excel cell data

John__Carter — Fri, 19 Jan 2024 09:10:00 GMT

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

RE: Split multiline excel cell data

KT — Fri, 19 Jan 2024 12:10:00 GMT

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

RE: Split multiline excel cell data

MichealCharron — Fri, 19 Jan 2024 14:05:00 GMT

@KT

Did you try the following?

Chr(13) & Chr(10)

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

RE: Split multiline excel cell data

KT — Fri, 19 Jan 2024 14:25:00 GMT

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

RE: Split multiline excel cell data

MichealCharron — Fri, 19 Jan 2024 15:22:00 GMT

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

RE: Split multiline excel cell data

Anton__Hosang — Wed, 24 Jan 2024 22:10:00 GMT

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

RE: Split multiline excel cell data

KT — Wed, 24 Jan 2024 23:31:00 GMT

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

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

RE: Split multiline excel cell data

John__Carter — Thu, 25 Jan 2024 10:50:00 GMT

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