How to turn surname and firstname around
Requirement:
The user is using this formula to extract the Surname and Firstname from a string where the Surname comes first followed by the Firstname and then some other information:
=TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",100)),200))
i.e. Bloggs Jim HCD Centre Nine Team 4
However, is there a way to modify this so that it returns the Firstname and then the Surname please? Instead of returning Bloggs Jim I'd like it to return Jim Bloggs if at all possible.
Solution:
=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",100)),100,100)&" "&LEFT(SUBSTITUTE(B2," ",REPT(" ",100)),100))
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to merge multiple excel files into one file in separate sheets with source file name |
| How to test if named range exists |
| How to compare 2 date ranges when name matches |
| How to use the namebox shortcut key |
| How to paste from multiple Excel workbooks into one workbook (Across the page & file names) |
| How to add error handling that will advise user if file name already exists AND offer user option to overwrite file |
| How to set cell as the name of the other open workbook |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
