How to separate data in a cell into different cells?

I am stuck with another doubt, can you help?

How can I separate address, telephone number, email id, website into different cells?

doubt.xlsx (8.5 KB)

You’ll have to use read cell (or a read range) activity to get the information to the robot. If you used read cell, this will be a string. If you used read range, it will be a datatable (which you will have to convert into string).

Assuming you went with read range, you can turn it into a string with the following: YourDatatable.Rows(rowindex).item(columnindex).ToString - assuming there is only 1 column and 1 row this would look like this: YourDatatable.Rows(0).item(0).Tostring

So now you have your string, but it has all the info in a single string and you want to have it separated out. To do this, you need to use some form of string manipulation. You can use strings.split() method, strings.substring() method, or a regular expression (regex) to pull out the specific info. It is up to personal preference and the input structure as to which one of the methods is best. I personally would go with split string method here if it stays consistent in strucutre like you show in your picture.

Strings.Split method will allow you to split the string on the environment.newline character with the following expression: String.split(YourText,environment.newline,StringSplitOptions.RemoveEmptyEntries)

This gives you an array of strings which should be the following where the # shows the index:
0: Address
1: 152-160 City RoadKemp HouseLondonEC1V 2NX
2: Telephone
3: 020 3602 4275
4: Email
5: info@aznos.co.uk
6: Website
7: www.aznos.co.uk

So for example to access the string containing the actual telephone number, you would use YourSplitStringVariable(3)

If you want to put it back into excel, you first need to put it into a datatable. To do this I would use the build datatable activity. Name the columns how you’d like and delete all the rows. I’ll call this new datatable dt1 from here on out.

Now use an ‘add data row’ activity. The properties should be as follows
ArrayRow: {YourSplitStringVariable(1),YourSplitStringVariable(3),YourSplitStringVariable(5),YourSplitStringVariable(7)}
DataRow: leave this blank
DataTable: dt1

Now use a write range activity to write dt1 into the excel file at the sheet and range you specify. Whatever range you put in is going to be the top-left of the datatable

1 Like

Hi @ADITYA_MUKHERJEE,

use a Read cell activity and store this to a variable after that use the following Regular Expressions to get the required data
Address
image
Telephone
image
Email
image
Website
image

if this solves your query then mark this as solution.
Happy Automation!
Regards,
Aditya

what are we storing in YourText?

YourText is a string variable of the entire original text. This is what you save the output to if using ReadCell activity, or what you put in the assign for YourText = YourDatatable.Rows(0).item(0).ToString

You can change the variable to be named anything you’d like. I just named them those thigns to make it more clear to you (e.g. YourText is the entire text you are trying to manipulate, YourDatatable is the datatable you extracted using read range, etc)

image
I am having this error brother.

Hi, i tried with regex but it is showing error.

Hi @ADITYA_MUKHERJEE

Implement the above expression in uipath studio. This might work there give it a try.
Regards,
Aditya

It’s not providing the desired result. I am using matches activity.

Hi @ADITYA_MUKHERJEE

Are you using for each to display each item in matches?
Regards,
Aditya

Change it to be Strings.Split instead of String.Split

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.