Excel: Split A Column Into Multiple Columns

[Brett_Johnson]

Hello,

I have an Excel WB with Sheet1 that has a table called “Leads” where the first column in labeled “Address”

I have inserted additional columns labeled “City”, “State”, and “Zip” to the right of “Address” which are all empty.

In the “Address” column, I have a list of addresses that looking like this

123 SUMMER ST, MINNEAPOLIS, MN 55499

I want to split that into the “City”, “State”, and “Zip” by comma, comma, and space.

I’ve looked at many posts and haven’t seen a solution for this within UiPath Studio.

Your help is greatly appreciated.

Brett

[Yoichi]

Hi,

Can you try the following expression?

m = System.Text.RegularExpressions.Regex.Match(strAddress,"(?<Address>^[^,]+),\s*(?<City>[^,]+),\s*(?<State>\w+)\s*(?<Zip>\d+)")

Sequence.xaml (5.8 KB)

Regards,

[Brett_Johnson]

To clarify, I need it to look at cell A2, then split it to Cells B2, C2, and D2 and increment and so on until there are no more addresses.

I would love to use the doc found here…

Text to Columns

…TextToColumns option for activities within Studio.

Please advise.

[rahulsharma]

If your address is stored in strAddressString

strCity = Split(strAddressString,“,”)(1)

strState = Split(Split(strAddressString,“,”)(2)," ")(0)

strSplit = Split(Split(strAddressString,“,”)(2)," “)(1)
–=–
You can use the text to column in the same manner
You need to first use , as delimiter and you will get the city in second column
and then use space (” ") as delimiter for third column and you will get rest of the two values

[adarsh_kotagiri]

Hii
Please check thisForum_Problem.zip (8.2 KB)

[dari_donkuro]

Split the string by “,” into an array of three. The address is the first array element, City is second, the 3rd is state and zip, so split by " " or substring first 2 characters for state, the remaining for zip code.

Good luck.

[Brett_Johnson]

Thank you all for your code and examples. I’ve been able to cobble together a solution that is working well, but with one small glitch I can’t seem to figure out.

I’m storing what’s shown in TempAddy in the strAddyTempString variable

I am then splitting that variable into multiple variables…

TempAddy
strAddyTempString = CurrentRow.ByField(“TempAddy”)

Address:
strAddress = Split(strAddyTempString,“,”)(0)

City:
strCity = Split(Split(strAddyTempString,“,”)(1)," ")(1)

State:
strState = Split(Split(strAddyTempString,“,”)(2)," ")(1)

Zip:
strZip = Split(Split(strAddyTempString,“,”)(2)," ")(2)

Then I am using multiple “Write Cell” statements fill out the spreadsheet.

Only problem I have, as you can see from the example, the “Address” field has a “new line” in it.

Why is that being added and how can I avoid it/remove it?

Based on the above, what am I missing?

Thanks again!

Brett

[jack.chan]

@Brett_Johnson

strAddress = Split(strAddyTempString,“,”)(0)

try
strAddress = Split(strAddyTempString,",")(0).Replace(Environment.NewLine,"")

[Brett_Johnson]

@jack.chan

Split(strAddyTempString,“,”)(0).Replace(Environment.NewLine,“”)

That didn’t work. Same result.

A non-elegant workaround way to solve would be to hit F2 > Back > Enter on the cell after the Write Cell “strAddress” activity, but I can’t seem to get that to work with the various key trigger/hot key activities.

Any suggestion on that or otherwise?

[msan (Michel SAN)]

Hi @Brett_Johnson ,

I’d rather use Regex with groups like @Yoichi did: you don’t have to deal with unwanted chars and you keep your code more readable. Otherwise, try to use Trim like

strAddress.Trim

or

Split(strAddressString,",")(1).Trim

[Brett_Johnson]

@msan Split(strAddressString,“,”)(1).Trim

That didn’t work at all. The code was incomplete. Do I need to add something at the end of Trim?

Per your recommendation, I figured out how to use the cleaner @Yoichi method in the image below, I still get the same result in the excel file “Address” field. There is a carriage return/new line added when the value is written. I am looking at the “Write Cell” command and there’s nothing in there that’s set to add an additional new line.

Back to my earlier comment, which activity would I need to use within “Excel For Each Row” to be able to perform key triggers? If I can just F2 > Back > Enter, then I have a workaround.

Please advise. Thanks for your help.

[msan (Michel SAN)]

@Brett_Johnson could you please copy/paste the address (with the extra lines) into a text file and attach that text file to your following post?

[Brett_Johnson]

Address.txt (20 Bytes)

It looks like the prior method was adding “” around the address.

Using “Write Cell” with Split(strAddyTempString,”,”)(0).ToString.Trim solved it. No more extra line.

Based on your trim idea earlier as well as the the following post:

Extract part of string before the comma "," and put in a variable. Then extract string after dash "-" and put in variable

I need to extract two parts of a string and put them in a variable. For example, my format is “lastname,firstname-1234”. I need to extract the last name and put it in a variable and then extract 1234 and put it in a variable.