importing multiple word names into Excel

hi Folks
Just wondering  if I wanted to import names from Salesforce e.g. Anne Marie Farrell - if I use Text to Columns it will import it as
Anne /Marie/Farrell i.e. into 3 cells whereas I'd like it to come in as Anne Marie/Farrell...

Any suggestions?

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.

Professor JMicrosoft Excel ExpertCommented:
do you want the solution with formula instead of text2col?
Professor JMicrosoft Excel ExpertCommented:
as far as know, you can achieve this with followings

use a Fixed Width if all of the names in your column has more or less same length.

use a substitude formula to determine which delimter to be the criteria then use t2c

lastly use only formula, not T2C
agwalshAuthor Commented:
Yes, I'd had a look at the Fixed practically speaking how would I use a substitute formula to extract the name as specified above i.e. I want Anne Marie/Farrell not Anne/Marie/Farrell?

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Katie PierceCommented:
I did Text to Column to get the three columns, then entered the following formula to get Anne and Marie together: =A2&" "&B2

However, if your sreadsheet contained some three-name people and some two-name people, you'd run into a problem there, so I added a couple columns with IF statements to get the last names aligned in one column.  See attached.
Katie PierceCommented:
To follow up, if you used the version with the additional IF statements, the combination formula would then look to those, not the first rows: =D2&" "&E2

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
agwalshAuthor Commented:
This works but would have liked a little more explanation of the substitute function as mentioned by ProfessorJimJam ..thanks.
Professor JMicrosoft Excel ExpertCommented:

here is the solution by Substitute with the explanation

Substitute is really handy for replacing a specific (or all) instances of a specific character (or string), without knowing  specific location of what you are substituting.

so in this case, substitute triggers where to start, which is the space in this case.

i personally use the substitute and rept function quite often, it can be used in many ways to get field from delimited text string.

so, on your question, assuming that full name is in A1 that has first name middle name and last name also and you want to excerpt the first name and middle name in B1 and last name in C1 then you can use the following formula

this a joint of two  with ampersand  see bold =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),1*99-98,99))&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),2*99-98,99))    in B1

 and then in C1 =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),3*99-98,99))

which in other words =TRIM(MID(SUBSTITUTE(A1,delimiter,REPT(" ",99)),fieldnumber*99-98,99))  

Moreover, you have chosen the B grade closing this question, which i believe is incorrect. normally, all answers should get a A grade.   B option is rarely used, in circumstances where the Experts contributions are at the minimum.
agwalshAuthor Commented:
Thanks for that explanation...I appreciate it. I gave a B grade because of the omission of the explanation of this piece. But I'm happy to revise upwards based on this. :-) Let me know what I need to do to revise upwards.
agwalshAuthor Commented:
Great stuff. Thanks :-)
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.