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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi

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


Tony
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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","")))))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
....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))
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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
Back
Top