Remove last characters in excel

I have a column with a lot of social security numbers. its stands like this: " xxxxxx-xxxx". in each cell.

So how do i remove the first space, and then remove “-xxxx”. So i only have 6 character left, and without the space in the front?

My data is loading into excel from a https request.

Can anyone please help? :slight_smile:

Can you please give an example of that? i cant make it work

the regex way with Regex.Replace:


(?<=\b\d{6})\-\d{4}

Hi!

can you provide the 2 to 3 sample inputs.

you can achieve that by using regex and also string manipulation.

what you have to do is just provide the sample input!

Regards,
NaNi

Hi,

Use simple string manipulation below. You will get your desired result. Thanks.

YourString.Split("-",c)(0).trim

Hi @christian.bendtsen

Sorry,I have made mistake on Expression!

Refer to the screen shot below!

Regards

2 Likes

Yes i found that method as well, but how do i do that in an excel application?

Hi,

if you want to perform this operation in excel itself use the below sample formula.

=TRIM(LEFT(A2,SEARCH("-",A2,1)-1))

A2 should be your number eg: 123456-1234

you could use write cell activity to write this formula in starting of the cell and after that use auto fill to fill the data of the entire column. if you have any doubts please let us know. thanks.

All right i try, and then i let you know.
Another question, how do i trim my entry sheet in excel, i can see i have the space in every cell, and i want to remove that. :slight_smile:

Hi!

Follow the steps below.

1.Read excel using read range output as dt1.

if you wants to remove the spaces

  1. take one foreach row in data table
    3.Take one assign activity and write like this Variable(Name)=CurrentRow(“ColumnName”).ToString.Trim
    4.take one more assign Name=name.Split("-",c)(0).trim

Regards,
NaNi

Can you please show me how in a uipath file? :slight_smile:

Can you please give an exampel of this in a file?

Error ERROR Validation Error Compiler error(s) encountered processing expression “”=TRIM(LEFT(G2,SEARCH("-",G2,1)-1))"".
Option Strict On disallows implicit conversions from ‘String’ to ‘Double’.
Option Strict On disallows implicit conversions from ‘String’ to ‘Double’.

getting this error when i try

Hi @christian.bendtsen

Refer to xaml below for similar reference!

new_test.xaml (6.9 KB)

Regards

Hi,

Please find the attached work flow. it might help your requirement. thanks. the issue you mentioned it is resolved in the work flow. thanks

ExcelDemo2.zip (9.1 KB)

For trim the whole sheet, and remove the space in each cell, how do i do that? :slight_smile:
can you please show me

I am getting error

Hi,

Please download the sample project which i have created you will get solution in that. thanks

Thats perfekt, thanks a lot!

I wanna assign another column “name”, and here i need to split firstname in one row, and middle name and last name to a second row. how can i do that?

Hi @christian.bendtsen

Could you share me the screen shot of the column that contains the value!

Regards