manipulate order of text in cell?

[jevremovj]

Hi - I am using Microsoft Office Excel 2003. I am trying to write a formula that takes data in a cell that is in the wrong order then what I need it to be in... and put it in the correct order.

For example, in column P I have names that are in the order of: Last Name, First name... but in order for my other lookup formulas to work... I need to write a formula in column BB that translates what is in column P to be: First Name Last name (ie. result would be Frank Jones instead of Jones, Frank).

Any advice would be appreciated.

 

[SteveO59L]

Use Data, Text to Columns to split the data. Then format as required

 

[ctrlaltdel]

Try this

Assuming your name formatted last name, first name is in cell P1

The formula you would enter in cell BB1 would be:
=MID(P1,FIND(", ",P1)+2,LEN(P1))&" "&LEFT(P1,FIND(", ",P1)-1)

 

[jevremovj]

Thanks! the mid formula works perfectly (I tweaked the reference near the end of the formula above which says "AP1" to be "P1" and it worked... wasn't sure if it was supposed to be P1, but it worked.