HELP - EASY

[moosemessier]

hi.

i am new. i want to ask someone the functions used for string extraction. i am using excel2000 and have a question.

i am manipulating data in a spreadsheet. i want to learn how to extract a last name at the end of a string and swap with the first.

ex.

William H. Dunham

i used the left,right,search functions but cannot extract dunaham becuase of multiple blanks.

ex - (RIGHT(A2,SEARCH(" ",A2))

returns- . Dunham

because the search is reading left to right and not right to left.

help!?

thanks soooooooo much.

 

[Aladin Akyurek]

On 2002-03-04 13:38, moosemessier wrote:
hi.

i am new. i want to ask someone the functions used for string extraction. i am using excel2000 and have a question.

i am manipulating data in a spreadsheet. i want to learn how to extract a last name at the end of a string and swap with the first.

ex.

William H. Dunham

i used the left,right,search functions but cannot extract dunaham becuase of multiple blanks.

ex - (RIGHT(A2,SEARCH(" ",A2))

returns- . Dunham

because the search is reading left to right and not right to left.

help!?

thanks soooooooo much.

=RIGHT(A2,LEN(A2)-SEARCH("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))&", "&LEFT(A2,SEARCH("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

where A2 houses a name entry to be reversed.

 

[Ian Mac]

On 2002-03-04 13:38, moosemessier wrote:
hi.

i am new. i want to ask someone the functions used for string extraction. i am using excel2000 and have a question.

i am manipulating data in a spreadsheet. i want to learn how to extract a last name at the end of a string and swap with the first.

ex.

William H. Dunham

i used the left,right,search functions but cannot extract dunaham becuase of multiple blanks.

ex - (RIGHT(A2,SEARCH(" ",A2))

returns- . Dunham

because the search is reading left to right and not right to left.

help!?

thanks soooooooo much.

not sure if you want to keep the initial letter, if not use:

=MID(MID(A1,FIND(" ",A1)+1,100),FIND(" ",MID(A1,FIND(" ",A1)+1,100))+1,100)&", "&LEFT(A1,FIND(" ",A1)-1)

this also relies on there always being an intial, if this is not the case you'll have to use the above formula AND:

=MID(A1,FIND(" ",A1)+1,100)&", "&LEFT(A1,FIND(" ",A1)-1)

as two arguements of an if statement.

Any help? I think Aladin's answer is nicer but I thought I'd have a stab.

 

[moosemessier]

thanks so much guys.
i will try the formula now!

 

[moosemessier]

that formula (1st one) works great.

question- how do i use it for the following format?

dunham, michael j.

 

[moosemessier]

bump

 

[moosemessier]

bump

 

[Aladin Akyurek]

On 2002-03-05 07:36, moosemessier wrote:
that formula (1st one) works great.

question- how do i use it for the following format?

dunham, michael j.

The formula I proposed does exactly that.

Aladin

 

[moosemessier]

no it does:

j, dunham, mike

 

[moosemessier]

bumpitty bump bump