Solved: splitting a part of text into another column after last space

[Beatrix]

Hi Everyone ,:hi:

I have a list of names in one column in xlsx. First name/middle name/ surname. However some of them don't have middle names. I need to split the surnames into next column. So last text after the last space should be cut and paste. Text to columns doesn't work for this case and I've thought I can use vba to do this. Can anyone help me ?:help

Cheers
Yeliz

[Kenneth Hobs]

I have seen names anywhere from 2 to 5 words long. This is why database design should have fields for First Middle and Last and possibly surnames and titles as well. It can get complicated if you don't layout the database properly.

For your case, give us a few examples. I can see at least 2 cases:
1. Split 3 word string into 3 columns.
2. Split 2 word string in column A to columns B and D.
etc.

I have posted examples like these in the past. Each need varies so I prefer feedback before offering a solution.

[Beatrix]

Hi Kenneth ,

Thanks very much for your quick response:thumb I made a list by picking different name formats from the list. There are 4 different databases. They all have seperate design that's why data format drives me mad. It's very complicated as you said. I've attached a sample file. Hope this makes it clear..

I appreciate for your help :bow:



I have seen names anywhere from 2 to 5 words long. This is why database design should have fields for First Middle and Last and possibly surnames and titles as well. It can get complicated if you don't layout the database properly.

For your case, give us a few examples. I can see at least 2 cases:
1. Split 3 word string into 3 columns.
2. Split 2 word string in column A to columns B and D.
etc.

I have posted examples like these in the past. Each need varies so I prefer feedback before offering a solution.

[mancubus]

the names in your sample file shows one pattern.
find the position of the last " " (space) in the cell.
the words before this position are the names and the word after this position is the family name.

assuming name data are in column A, following procedure splits the full names to columns B and C.

but im not sure all the two-word last names have hypens in between. users may fail to write them when entering data. or persons may originally have two-word last names without hypens.

so you should keep in mind that the below code splits only one-word last names and the two-word last names with hypens correctly.


Sub Split_Names()

Dim LR As Long, i As Long, pos As Integer
Dim ws As Worksheet

Set ws = Worksheets("NameOfTheSheetWithData")
With ws
LR = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To LR
pos = InStrRev(.Range("A" & i), " ")
.Range("B" & i) = Left(.Range("A" & i), pos - 1)
.Range("C" & i) = Right(.Range("A" & i), Len(.Range("A" & i)) - pos)
Next
End With

End Sub

[Beatrix]

Hi Mancubus ,

Tested, working perfect!!:thumb
Thanks very much for your time:bow:

I need to edit this script as I realised I just need to split the last word as a family name. Therefore I need to find the position of the last space, comma, or hyphen.

I've changed the below line like this but it wasn't a good guess apparently:devil2:

from:
pos = InStrRev(.Range("G" & i), " ")
to:
pos = InStrRev(.Range("G" & i), " " & "," & "-")
any suggestions???:think:


the names in your sample file shows one pattern.
find the position of the last " " (space) in the cell.
the words before this position are the names and the word after this position is the family name.

assuming name data are in column A, following procedure splits the full names to columns B and C.

but im not sure all the two-word last names have hypens in between. users may fail to write them when entering data. or persons may originally have two-word last names without hypens.

so you should keep in mind that the below code splits only one-word last names and the two-word last names with hypens correctly.


Sub Split_Names()

Dim LR As Long, i As Long, pos As Integer
Dim ws As Worksheet

Set ws = Worksheets("NameOfTheSheetWithData")
With ws
LR = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To LR
pos = InStrRev(.Range("A" & i), " ")
.Range("B" & i) = Left(.Range("A" & i), pos - 1)
.Range("C" & i) = Right(.Range("A" & i), Len(.Range("A" & i)) - pos)
Next
End With

End Sub

[mancubus]

i think "-"s are specific to family name. i would keep them.

i think some of your tables contain names in "FamilyName, Names(s)" format.


pos = InStrRev(.Range("G" & i), ",")
in this case, Left function returns Family Name.


you can easly parse strings using Left, Right, Mid, Len, etc vba functions.

[Beatrix]

yep you are right.:yes I should keep them as hyphens are a part of the surname.

I've changed the script line as you said.:thumb

pos = InStrRev(.Range("I" & i), ",")
thanks very much again :friends:



i think "-"s are specific to family name. i would keep them.

some your tables contain names in "FamilyName, Names(s)"


pos = InStrRev(.Range("G" & i), ",")
ın this case, Left function returns Family Name.

you can easly parse strings using Left, Right, Mid, Len, etc vba functions.