A Complex Data Splitting Task

ToddOdd

New Member
Joined
Mar 4, 2009
Messages
4
I have a column of information, in 1 worksheet, that has name data that literally looks like this:

JohnSmith - Owner

The first and last names are not separated by anything, only showing up as different words because of capitalization. The "Job Title" in this column is separated by a space, a hyphen, and a space (" - ")

What I would like to do is split this data up into 3 different columns - one column with the parsed out first name, one with the last name, and one with the Owner. A function or macro would be ideal, but I am not sure how to handle this because of the odd situation with the capitalization.

Any suggestions?

ToddOdd
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
Hi ToddOdd
Welcome to the board

A function or macro would be ideal, but I am not sure how to handle this because of the odd situation with the capitalization.
I don't see why you say it's an "odd capitalization". You can simply loop through the characters and check if they are upper- or lowercase.

Another option is to use a regular expression. For example, assuming the data in column A starting in row 2, try::

Code:
Sub SplitCell()
Dim rRng As Range, rCell As Range
 
Set rRng = Range("A2", Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
With CreateObject("VBScript.RegExp")
    For Each rCell In rRng
        .Pattern = "([A-Z][a-z]*)([A-Z][a-z]*) - ([A-Za-z]+)"
        If .test(rCell) Then rCell.Offset(, 1).Resize(, 3) = Split(.Replace(rCell, "$1 $2 $3"))
    Next rCell
End With
Application.ScreenUpdating = True
End Sub
 

ToddOdd

New Member
Joined
Mar 4, 2009
Messages
4
A thing of beauty and elegance. Thank you for saving me hours of time!!!

ToddOdd
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
I'm glad it helped.

I noticed that I defined the regex pattern inside the loop and it should have been outside. This will have no effect in the result but it's more efficient and makes more sense:

Code:
Sub SplitCell()
Dim rRng As Range, rCell As Range
 
Set rRng = Range("A2", Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
With CreateObject("VBScript.RegExp")
    .Pattern = "([A-Z][a-z]*)([A-Z][a-z]*) - ([A-Za-z]+)"
    For Each rCell In rRng
        If .test(rCell) Then rCell.Offset(, 1).Resize(, 3) = Split(.Replace(rCell, "$1 $2 $3"))
    Next rCell
End With
Application.ScreenUpdating = True
End Sub
Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,099,115
Messages
5,466,769
Members
406,498
Latest member
ddreadedd

This Week's Hot Topics

Top