Solved How Can You Break Down Text Into Different Sections

January 24, 2013 at 03:11:09
Specs: Windows 7

Hi Can anybody help with this problem

I have a text field in A1 which reads

Mr James John Smith

Is there a way to move this data into seperate columns

Mr 1st Name 2nd Name Surname

Just to make it more complicated they may not have a middle name

Many Thanks


See More: How Can You Break Down Text Into Different Sections

Report •


#1
January 24, 2013 at 06:33:41
✔ Best Answer

You might be able to use the text to columns feature, with space as the delimiter. It should be under the data menu. I would try with one box first, and see the preview to see if it helps.The only issue I see is the no middle names.

:: mike


Report •

#2
January 24, 2013 at 06:59:16

First thing you will need to do is get rid of the Honorific Mr.
Simplest way is to do a Search/Replace.

Then:

To get First Name:

=LEFT(A1,FIND(" ",A1,1))

To get Last Name, with NO Middle Name or Initial

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

To get Last Name WITH Middle Name or Initial

=RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

To get Middle Name

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1))

See how that works.

MIKE

http://www.skeptic.com/


Report •

#3
January 24, 2013 at 08:03:06


Report •

Related Solutions

#4
January 24, 2013 at 09:19:54

Hi Many Thanks for that

I used the text to columns feature

Using the ISBLANK if there was nothing in the surname I replaced the previous name

Think that should work

Thanks


Report •

#5
January 24, 2013 at 09:21:37

Many thanks

This should work


Report •

#6
January 24, 2013 at 09:43:36

Hi

It seems to have lots of useful stuff as well

Thanks


Report •


Ask Question