Is there a regexp for Excel?

February 9, 2006 8:49 AM

ExcelFilter: How do I convert a column of names formatted Smith, John into John Smith. Feasible?
posted by furtive to computers & internet (8 comments total)

From the Data menu, choose Text to Columns using the comma as a delimiter. This will split your first column into two. Then use the CONCENTATE function in a third column to join them back together in the right order.
posted by justkevin at 8:53 AM on February 9, 2006

Or use @Search to locate the comma (assuming you always split on the first comma in the text) and use MID and LEFT to extract the pieces to the right and left of the comma:

=MID(A1,(SEARCH(",",A1)+2),100)&" "&LEFT(A1,(SEARCH(",",A1))-1)
posted by Gortuk at 9:01 AM on February 9, 2006

justkevin's solution is pretty cool, I was going to suggest a brute force method using LEN, FIND, LEFT, RIGHT as separate cell formulae.
posted by JJ86 at 9:08 AM on February 9, 2006

any idea how to go the other way? 2 columns, 1 first name, 1 last name need to go into one column. (sorry to hijack)
posted by SheIsMighty at 9:45 AM on February 9, 2006

Oops, I re-read the first answer and that may be my answer. I'll try it.
posted by SheIsMighty at 9:46 AM on February 9, 2006

Spelling counts: it's "CONCATENATE" not "CONCENTATE". But & does the same thing and you don't have to remember how to spell it: A1&B1 does the same thing as CONCATENATE(A1,B1).
posted by DevilsAdvocate at 10:28 AM on February 9, 2006

One more important thing: after you use CONCATENATE, you need to divorce the result from the originals (assuming that you mean to delete the original columns and leave yourself with only the one assembled column.) To do this, simply select all of the cells with the correctly formatted name, then choose Edit -> Copy. Then, with those same cells selected, choose Edit -> Paste Special, and from the resulting dialog box, choose Paste Values.

BTW, you don't actually have to use the CONCATENATE function, either. You can simply say =A1 & " " & B1, which takes the value of A1, adds a space, and then the value of B1. It does the same thing as CONCATENATE.
posted by robhuddles at 12:44 PM on February 9, 2006

Me too, JJ. I worked it all out and then came back to post it, but the more elegant solution was already up.
posted by mbd1mbd1 at 12:56 PM on February 9, 2006