asked on

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?

Thanks

Professor J

do you want the solution with formula instead of text2col?

SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

agwalsh

ASKER

Yes, I'd had a look at the Fixed width...so 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?

Thanks

SOLUTION

Katie Pierce

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Katie Pierce

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

agwalsh

ASKER

This works but would have liked a little more explanation of the substitute function as mentioned by ProfessorJimJam ..thanks.

Professor J

Agwalsh,

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.

agwalsh

ASKER

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.

agwalsh

ASKER

Great stuff. Thanks :-)