topic RE: Split Number and Text for Multiple Lines in Product Forum

Split Number and Text for Multiple Lines

Lily.L — Fri, 29 Sep 2023 09:11:00 GMT

Hi there,

I want to split numbers and text and store them in different columns; reference number and reference text.

1/ I have a reference text box which has multiple lines; for example

In "Pet Category" Reference, we have:

[8899] cat - cat food

[9988] dog - dog food

[7788] pet - pet food

[9977] cow - cow milk

My BP code for splitting numbers and text is shown below. However, it only works for [8899] cat - cat food, but not others [the other references are not split - still in original form) Note: Since the code, Mid([Reference Output],3,6) is for extracting numbers and Replace([Reference Output],Left([Reference Output],9),"") is for extracting text. The result will be [8899] and cat - cat food [which are separate for reference number and reference text columns].

2/ My reference text box outcome will come in different ways; some of them will have 1 line, some of them will have 2 lines some of them will have 3 lines, and so on.

In this case, what's the best way I should do to split text and number; according to their reference text box?

Thank you so much!

------------------------------
Mia Mikela
------------------------------

RE: Split Number and Text for Multiple Lines

John__Carter — Fri, 29 Sep 2023 10:50:00 GMT

Maybe the first step is to split the text into a collection of lines (Utility - Strings). Then loop through the collection and split the line into number & text.

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

RE: Split Number and Text for Multiple Lines

MichealCharron — Fri, 29 Sep 2023 17:46:00 GMT

@Mia Mikela,

You can use the action "Extract Regex All Matches" from Blue Prism's Utility - Strings VBO (Blue Prism Digital Exchange - Utility Strings) to extract that information into a collection.

You can use the Regex pattern:

(?<ReferenceNumber>\[\d{4}\])\s+(?<ReferenceText>[^\r\n]+)

What you will end up with is a collection of data that of all matches no matter how many lines your original text has:

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

RE: Split Number and Text for Multiple Lines

Lily.L — Fri, 29 Sep 2023 20:03:00 GMT

Thanks @Micheal Charron,

May I ask, instead of splitting numbers and text , how about keeping the reference output in its original form - but in a separate line when added to the collection

What Regex pattern: I should use?

Note: Given the reference output looks like this:

[8899] cat - cat food
[9988] dog - dog food
[7788] pet - pet food
[9977] cow - cow milk

However, when I add them to the collection, they are looked like this: "[8899] cat - cat food [9988] dog - dog food [7788] pet - pet food [9977] cow - cow milk". So I want to split the line and keep its original form.

Thanks a lot!

------------------------------
Mia Mikela
------------------------------

RE: Split Number and Text for Multiple Lines

Lily.L — Fri, 29 Sep 2023 20:03:00 GMT

Thanks @Micheal Charron,

May I ask, instead of splitting numbers and text , how about keeping the reference output in its original form - but in a separate line when added to the collection

What Regex pattern: I should use?

Note: Given the reference output looks like this:

[8899] cat - cat food
[9988] dog - dog food
[7788] pet - pet food
[9977] cow - cow milk

Thanks a lot!

RE: Split Number and Text for Multiple Lines

MichealCharron — Fri, 29 Sep 2023 20:15:00 GMT

@Mia Mikela

If you are just looking to split text by lines into a collection, as @John Carter pointed out, I would use the "Split Lines" action in Blue Prism's "Utility - Strings". Regex would be overkill in that situation.

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

RE: Split Number and Text for Multiple Lines

Lily.L — Fri, 29 Sep 2023 21:26:00 GMT

Hi can you please have a look at my steps, I follow the steps, add loop, add split text. but it still doesn't work correctly

and result look like this: [8899] cat - cat food[9988] dog - dog food

------------------------------
Mia Mikela
------------------------------