Splitting up first and last names in text field
Posted by Mark G. on August 24, 2001 7:56 AM
I am working on an excel spreadsheet that will take a column of full names in various formats from a database(ex. Mary Jones, Michael T. Ford, Bobby Sue MacCalister) and return two columns - one for the first part of the name (ex. Mary, Michael T., Bobby Sue) and one for the last name (ex. Jones, Ford, MacCalister).
Any ideas on how I can do this?
Posted by Aladin Akyurek on August 24, 2001 9:00 AM
Posted by Ram Krishnan on August 24, 2001 9:17 AM
Use DATA | Text to Columns
Then use concatenate to get it exactly the way you want the names to display
Caution: Make sure that you have enough space to use the Text to Columns feature. If the largest name you have space wise is Billy Joe A. Barnes Jr (5 spaces) you should have four empty columns to the right of the column that contains the names.
Also if you do any concatenating, remember that you will be getting formulas. You need to select your formulas and then use the copy command and then use the paste special command to convert formulas to values. You can then delete all the excess columns.
Let me know if this is useful and whether you need more detailed instructions.
Regards,
Ram
Posted by Eric on August 24, 2001 1:34 PM
Isn't he asking to split names in one column into two (NT)?
Posted by Aladin Akyurek on August 24, 2001 1:44 PM
Yes...
Eric,
At 27646.html
I describe how to get the last name (in B2)
and this additional formula takes care of the rest of the name:
in C2 enter: =LEFT(A2,LEN(A2)-LEN(B2)-1)
Aladin
Posted by Eric on August 24, 2001 1:50 PM
Oops, sorry... thought you were giving two different options there
I should read more carefully! I must have Friday disease.
BTW that reversetext UDF is great, thx for making it :-)
I must have Friday disease or something
Posted by Mark G on August 24, 2001 10:47 PM
Hi Aladin - I read the link you sent me and I am anxious to try your set up. I don't know how to create a user-defined funtion though. When I look under insert functions, user-defined is a category, but how do I actually create the function called REVERSETEXT(text)? Any help would be appreciated.
Posted by Aladin Akyurek on August 25, 2001 1:46 AM
Adding an UDF to a workbook
> Hi Aladin - I read the link you sent me and I am anxious to try your set up. I don't know how to create a user-defined funtion though. When I look under insert functions, user-defined is a category, but how do I actually create the function called REVERSETEXT(text)? Any help would be appreciated.
Mark,
It's not too difficult.
Open your workbook where you want to have this UDF. Close all other workbooks, if any.
Activate Tools|Macro|Visual Basic Editor.
Go to the Project-VBA Project window/pane.
Double click on "ThisWorkbook".
You'll get a pop window with things like: General and Declarations.
Copy the UDF code and paste it in the open space that you see on the latter window.
Activate File|Close and Return to Microsoft Excel.
You're done. You can use REVERSETEXT anywhere you want within your workbook.
Aladin
Posted by Aladin Akyurek on August 25, 2001 2:21 AM
Correction to (Re: Adding an UDF to a workbook)
ACTIVATE INSERT|MODULE.
DOUBLE CLICK ON "Module1".
