DougStroud
Well-known Member
- Joined
- Aug 16, 2005
- Messages
- 2,976
- Office Version
- 365
- Platform
- MacOS
Column B, B3-B23, swap positions of last name and first name which are separated by a comma?
Thanks...
ds
Thanks...
ds
Excel 2012 | ||||
---|---|---|---|---|
B | C | |||
3 | Smith, Andrew | Andrew Smith | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3 | =SUBSTITUTE(B3,LEFT(B3,FIND(",",B3)-1)&", ","")&" "&LEFT(B3,FIND(",",B3)-1) |
Excel 2007 | ||||
---|---|---|---|---|
B | C | |||
3 | Smith, Andrew | Andrew Smith | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3 | =RIGHT(B3,LEN(B3)-FIND(", ",B3)-1)&" "&LEFT(B3,FIND(", ",B3)-1) |
Excel 2007 | ||||
---|---|---|---|---|
B | C | |||
3 | SMITH, Andrew | Andrew Smith | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3 | =RIGHT(B3,LEN(B3)-FIND(", ",B3)-1)&" "&PROPER(LEFT(B3,FIND(", ",B3)-1)) |
Thanks guys... This community is the most giving and generous group of people, always willing to help and asks for nothing in return. Thank you.
If I could ask one more thing for the same set of data. All the last names are capitalized, is it possible to change the letters in the last name to lower case after the first one?
Excel 2012 | ||||
---|---|---|---|---|
B | C | |||
3 | SMITH, ANDREW | Andrew Smith | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3 | =PROPER(SUBSTITUTE(B3,LEFT(B3,FIND(",",B3)-1)&", ","")&" "&LEFT(B3,FIND(",",B3)-1)) |
Actually, this still-compact formula would be better as it would protect against people with first names that are made up of two individual names combined (like a friend of mine whose legal first name is "Mary Ann") as well as people whose last names were composed of two name (like someone I worked with whose last name was "Della Rossa")...Here is a shorter formula (with less function calls) for you to consider...
=MID(B3&" "&PROPER(B3),FIND(" ",B3)+1,LEN(B3)-1)