excel name format

I have a column of data whereby the name is included in a single string of text, in format:

surname, title forename initial (note the comma after surname)


mouse, mr micky m

I need a way to spread these name elements out into 4 seperate columns. is this possible?

There can be 4 elements to each name, i.e. if the user has more than one middle name/initial, so it isnt restricted to 4 each time.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you use text to column tool under data tab?
please see attached formula solution

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCost AccountantCommented:
I too would suggest DataText to Columns using space as the delimiter.

However you could also use formula:

If the name was in A2 then:
B2: Surname : =LEFT(A2,FIND(", ",A2)-1)
C2: Title : =LEFT(SUBSTITUTE(A2,B2&", ",""),FIND(" ",SUBSTITUTE(A2,B2&", ",""))-1)
D2: Forename : =LEFT(SUBSTITUTE(A2,B2&", "&C2&" ",""),FIND(" ",SUBSTITUTE(A2,B2&", "&C2&" ",""))-1)
E2: Initial : =SUBSTITUTE(A2,B2&", "&C2&" "&D2&" ","")

Se the atteched workbook for worked example.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

pma111Author Commented:
How would text to columns work though as there are 2 seperate delimeters, i.e. , and space, or do you recommend first doing the comma, then doing the space?
as i have attached in my earlier post with attachment, this solution is so easy and you do not need different formulas, it can be done with one formula.

assuming your data starts A2 then put the formula =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",99)),COLUMNS($A$50000:A50000)*99-98,99)) in B2 and drag it horizontally to columns and it will separate on those columns. see my earlier attachment.

why dont you use a single formula to take care of all of it.
SteveCost AccountantCommented:
I would just use space as the delimiter, then do a find (ctrl+f) replace (comma with nothing) on comma on the Surname after.
if you insist in using the text 2 column then  use only space as delimiter
SteveCost AccountantCommented:
That is a really nice formula could come in handy in all sorts of cases.
Could you explain how it works in a little detail.

easy the , let me break it down in to the pieces

first the Substitude formula replaces the each single space to 99 time more meaning the each space will be 99 spaces.
then Column A as you know return 1 then to extract the first name from the string   1 * 99 return 99 then 99-98 is again 1 so the mid function will take will start from the first char of the string up to 99 which means becuase we already replaces the single space with 99 speaces so mid function will take the first name along with its trailing spaces, so then the trim function removes all the trailing spaces and only remains is the first name.

now the trick is the extraction of second and third name in the string,  now the column A moving to Column B changes the value to 2 then 2*99 is 198 then 198-98 it is 100 and the starting position of MID function is 100 which is now beyong the lenght of first name and it will jump to the second name and then the same procedure of MID function 99 char and trim removing the spaces will do the job.

for the third name in the string the column will be COlumn C and then value is 3,  3 will be 3*99 = 297 - 98 =199 the mid function starting position will be 199 which is beyond the position of the second name.

and so on.

hope my explanation makes sense

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",99)),COLUMNS($A$50000:A50000)*99-98,99))
Saqib Husain, SyedEngineerCommented:
You can use the space and the comma as delimiters at the same time
SteveCost AccountantCommented:
@ExcelAddict... thanks for the explanation. As you say, simple once explained.

My only question would be why use COLUMNS($A$50000:A50000)  rather than COLUMN(A:A)?
Not a huge difference, but just wondering if there is a reason?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.