Remove last characters in excel

I want the first name to be in one column, and the rest in another column.

There is still a space infront of the first name

Hi @christian.bendtsen

How can we identify the first name and last name if it contains three word?

Like Kate Holm Hemso

First name =Kate Holm Last Name =Hesmo

Or

First name =Kate Last Name = Holm Hesmo

Need Clarification!

Regards

The first name Kate, should be in one column. and all the rest of the names, should be in an second column. So in the second we will have “Holm Hesmo”.

if the name was “Peter Bech Holm Hesmo”

first column: Peter
second: Bech Holm Hesmo

Do you understand what i mean? :smiley:

1 Like

Hi @christian.bendtsen

Please refer to the xaml below!

new_test.xaml (12.4 KB)

Regards

Sorry but nothing happens. i put “name” in CurrentRow(“name”).

Can it be because the name is starting with a space? like this " Kate Holm Hemso" in every cell?

Also… When i clean up the the social_sercurity_number, and put using Write Range at the end. All the other data, after that column moves.

Hi @christian.bendtsen

Could you share me the Sample Input file Screenshot and Expected outPut Screenshot?

Regards

witch of the “A2” should be my number?

Its says “#VALUE!” in the excel sheet

in the sample i mentioned A2 is your ID column xxxxxx-xxxx.

My header is “social_security_number”. thats what you mean?

yeah enter that column reference in the formula to extract the only 6 digits and trim the spaces in the beginning of the string.

when i assign formulaFinal with “=TRIM(LEFT(social_security_number,SEARCH(”+CHR(34). toString+Hiphen+CHR(34). toString+",G2,1)-1))"

it only gives me 0 in the entire column.

What do i do wrong?

Hi,

Try like below i think you have hardcoded column name. i assume G column represents your social security number.

“=TRIM(LEFT(G2,SEARCH(”+CHR(34). toString+Hiphen+CHR(34). toString+",G2,1)-1))"

numbers
cell

when i write: “=TRIM(LEFT(G1,SEARCH(”+CHR(34).ToString+Hiphen+CHR(34).ToString+",G1,1)-1))"

it gives me this error.
Sorry, i cant see where my problem is, can you? :smiley:

and if i change G1 to G2, it gives me 0 in all cells

Hi,

We cannot refer the same cells with the excel formula it will not give output. you have to use formula to reference some other column. if social security number in G column you have to keep this formula in another blank column like K2. Thanks

i see, but what do i do wrong?


dayofbirth

“=TRIM(LEFT(G2,SEARCH(”+CHR(34). toString+Hiphen+CHR(34). toString+",G2,1)-1))"

Hi,

As per the work flow everything good and the formula is working fine for me. what is the format type of the day of birth column. And also if we have single quote in the starting of the formula it will not work.

yes just a second

name1
name2
want it as it stands in the second picture.

im using a write cell activity in ui path, but i need to know the formula, can you please help?