Splitting Data into separate cells

annegrey

New Member
Joined
Nov 17, 2005
Messages
32
Hi

I have data in a column similar to this:

AJ Smith
Alan Andrews
J&L Matthews
Mr & Mrs KL Mulder
Mr Alan Pinkett
Mr & Mrs John Guymer

I want to extract the name or initial in each case - so the only way I can imagine if to write a formula that takes the text between the last space and the 2nd last space. Any ideas?

Thanks in advance
AG :rolleyes:
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

How would you handle multiple word surnames? ie Von Darkmoor, or Van De Velde???


Tony
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Book1
ABCD
1AJ SmithSmithAJ
2Alan AndrewsAndrewsAlan
3J&L MatthewsMatthewsJ&L
4Mr & Mrs KL MulderMulderKL
5Mr Alan PinkettPinkettAlan
6Mr & Mrs John GuymerGuymerJohn
Sheet2


In B1:

=REPLACE(SUBSTITUTE(SUBSTITUTE(A1,"Mr & Mrs ",""),"Mr ",""),1,FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"Mr & Mrs ",""),"Mr ","")),"")

In C1:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,B1,""),"Mr & Mrs ",""),"Mr ",""))

HTH
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If you are prepared to add another column to select which word you want, this custom function will work:
Code:
Function ExtractElement(strText As String, strSep As String, n As Integer)
    Dim x As Variant
    x = Split(strText, strSep)
    If n - 1<= UBound(x) Then
        ExtractElement = x(n - 1)
    Else
        ExtractElement = ""
    End If
End Function
Paste into a new code module (Alt + F11, Insert > Module, paste the code, Alt + Q to return to Excel)

Use the function as follows: =Extractelement(A2," ",B2) where A2 contains the name and B2 is the number of the word you want.
Book1
ABCD
1Word
2AJSmith1AJ
3AlanAndrews1Alan
4J&LMatthews1J&L
5Mr&MrsKLMulder4KL
6MrAlanPinkett2Alan
7Mr&MrsJohnGuymer4John
Sheet2




Denis
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Paste the code onto Module
use in cell like

If data in A1 then

Select B1:C1
and enter = FLNames(A1)
Confirm with Ctrl + Shift + Enter (Array formula entry)
Code:
Function FLNames(txt As String)
  Dim e As Variant, myTxt As String, FName As String, LName As String, x As Long
  
  myTxt = txt
  For Each e In Array("Mr & Mrs", "Mr And Mrs","Mr","Mrs","Miss", _
                               "Ms","Dr","Prof") '<- add as many as you want
    myTxt = Replace(myTxt,e,"")
 Next
 x = InStrRev(myTxt,Chr(32))
 FLNames = Array(Trim(Left(myTxt,x)),Trim(Mid(myTxt,x)))
 End Function
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
assuming data start in A1, paste this formula in B1 then copied down.
Code:
=LEFT(TRIM(IF(ISERROR(SUBSTITUTE(A1,LEFT(A1,FIND("&",A1)),""))=TRUE,SUBSTITUTE(SUBSTITUTE(A1,"Mrs",""),"Mr",""),SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,FIND("&",A1)),""),"Mrs",""))),FIND(" ",TRIM(IF(ISERROR(SUBSTITUTE(A1,LEFT(A1,FIND("&",A1)),""))=TRUE,SUBSTITUTE(SUBSTITUTE(A1,"Mrs",""),"Mr",""),SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,FIND("&",A1)),""),"Mrs","")))))
 

Smokeyham

Board Regular
Joined
Feb 1, 2006
Messages
118
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Splitting Text Column into mulitiple columns split names

The code is great, but there is an easier way to split a column with first and last names into mulitple columns. Use the Data - Text to Column feature. A wizard will come up and walk you through the process.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Re: Splitting Text Column into mulitiple columns split names

The code is great, but there is an easier way to split a column with first and last names into mulitple columns. Use the Data - Text to Column feature. A wizard will come up and walk you through the process.
Hi, Smokeyham

if the names are like
Mr & Mrs KL Mulder
Mr Alan Pinkett
you cannot apply this

since you entered the Board answering a question
you would miss a chance to get a nice
<font face="Courier New" size="+1" color="blue" style="background: url(http://www.balloondoggies.com/vonpookie/misc/sparkle.gif)">WELCOME TO THE BOARD !!!!</font>

kind regards,
Erik
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
....the only way I can imagine if to write a formula that takes the text between the last space and the 2nd last space. Any ideas?

To do exactly that, assuming none of your "words" are longer than 50 characters...

=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&TRIM(A1)," ",REPT(" ",100)),200),150))
 

Forum statistics

Threads
1,136,354
Messages
5,675,294
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top