Excel functions

[ryne100]

Ok usually I'm good at this kind of thing, but I cannot find the code for this. I need the names in my spreadsheet to go from First Last to Last, First. I'm fairly certain you can do this in access, but I can't figure out how to in Excel. Any ideas?

[endaround]

Are they in separate cells?

[ryne100]

No it's in the same cell.

 

Right now it's like this:

 

Ben Sheets

 

and I want it to be

 

Sheets, Ben

[endaround]

Well not sure I can help

 

You could try exporting it as a space delineated text file and the reimporting it to get the names into separate cells and go from there. Of course if you have a bunch of workbook dependent things going on that isn't much help.

[lcbj68c]

=B3&", "&A3

[rluzinski]

This assumes that the first and last name is separated by a space and there are no other spaces. Also, if the last name is greater than 10 characters, it will only return the first 10. The name is in the A1 cell:

 

=CONCATENATE(MID(A1,FIND(" ",A1,1)+1,10),", ",LEFT(A1,FIND(" ",A1,1)-1))

 

As always, there is probably a better way to do this.

[lcbj68c]

My previous code was if they were in two seperate cells....combines them with a comma in between my bad

[ryne100]

I found a macro online that does it, thanks guys.

[rluzinski]

Why would a macro be more desirable than doing it with a function?

[ryne100]

I just needed it once for a set of data, so I ran the macro that some guy posted online. Not easier at all, but it got it done.