Seperate first and last name within a cell

[CAM]

Hello,

I have a list of names in a column that contains Mr. or Mrs. First Name
and Last name. What I want to do is to have a seperate column for Mr. or
Mrs., a seperated column for the First Name and a seperate column for the
Last Name. Is there a formula in Excel to do this? Any tips or website to
visit will be appreciated. Thank you in advance.

Cheers

 

[Guest]

A programmatic way instead of a formula is appended. Before running the macro
you must select the range of cells containing the names you want to split.

If you are not familiar with VBA code (macros) then you need to add a code
module to the project and paste the code.

To add a code module and paste the code:
1. Enter the Visual Basic Editor either through Alt + F11 or through
Tools>Macros>Visual Basic Editor.
2. Click the Insert button in the VBE's menu bar.
3. Select Module
4. Now paste the following code to the new code module.

'********* Code follows **********
Sub SplitNames()
Dim c As Range
Dim i As Integer
Dim arr As Variant

For Each c In Selection.Cells
c.Value = Trim(c.Value)
arr = Split(c.Value, " ")
For i = LBound(arr) To UBound(arr)
c(1, i + 1).Value = arr(i)
Next
Next
End Sub
'******* End of code ********

Now you can select the macro and run it through Tools > Macros > Macro and
click the Run button. You can also assign the macro to a toolbar button.
Reminder: Before running the macro you must select the range of cells
containing the names you want to split.

Regards,
Greg

 

[Nick Hodge]

CAM

Generally things are not this simple with names (double-barrel names,
prefixes, suffixes, initials, etc) but try highlighting you column and then
go to Data>Text to Columns... and use delimited in the first screen of the
wizard and space in the next and then exit the wizard and it will parse your
data on the spaces.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

 

[CAM]

Thanks guys for the tip.

Cheers