Is there a regexp for Excel?
February 9, 2006 8:49 AM Subscribe
ExcelFilter: How do I convert a column of names formatted Smith, John into John Smith. Feasible?
Best answer: 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
=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
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
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
posted by SheIsMighty at 9:46 AM on February 9, 2006
Best answer: 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
posted by DevilsAdvocate at 10:28 AM on February 9, 2006
Best answer: 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
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
posted by mbd1mbd1 at 12:56 PM on February 9, 2006
This thread is closed to new comments.
posted by justkevin at 8:53 AM on February 9, 2006