Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL FORMULAS
This might be an incredibly easy solution to my problem,
but I cannot think of it !! I have a list of employees which is set out in the format of first name, then surname e.g. John Smith. I want to turn that around to read Smith John - is there a way of doing this ?? Thanks !! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL FORMULAS
Hi Michael!
One way is to set up a new column and use the formula: =RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1) Then use Copy Paste Special Values on the new column and delete the old one. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "MICHAEL KENT" wrote in message ... This might be an incredibly easy solution to my problem, but I cannot think of it !! I have a list of employees which is set out in the format of first name, then surname e.g. John Smith. I want to turn that around to read Smith John - is there a way of doing this ?? Thanks !! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL FORMULAS
If the list of names is one string "John Smith" Then you need to first "split" the name, pulling first and last name into each strFirst and strLast, then reconstruct the string with strLast + " " + strFirst. Of course that will not work well if there are other punctuation marks in the string or if there is a middle initial.
So you can do a find on the original text for a space (" ") this result will give you a number, the number represents how many characters into the string the space is. This number can also be used to get that number of LEFT characters which would be stored as the strLast name. The total number of characters in the string, minus the number returned by the find would give you the RIGHT part of the string to be stored as strFirst. I know it's not VB code, but this pseudo code should get you going. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL FORMULAS
If the data is consistant, then Menu-Data/Text to
columns/Delimted(Character, such as commas....)/Next/Space/Next/Finish -----Original Message----- This might be an incredibly easy solution to my problem, but I cannot think of it !! I have a list of employees which is set out in the format of first name, then surname e.g. John Smith. I want to turn that around to read Smith John - is there a way of doing this ?? Thanks !! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
E2007 formulas display as formulas, not results | Excel Worksheet Functions | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
ms-excel guidence notes, complete formulas in excel, How to runmacros in Ms excel | Excel Discussion (Misc queries) | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions |