Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

importing multiple word names into Excel

Posted on 2014-10-31
10
Medium Priority
?
135 Views
Last Modified: 2014-11-05
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
0
Comment
Question by:agwalsh
  • 4
  • 3
  • 2
9 Comments
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40415493
do you want the solution with formula instead of text2col?
0
 
LVL 27

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 664 total points
ID: 40415500
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
0
 

Author Comment

by:agwalsh
ID: 40415712
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Assisted Solution

by:Katie Pierce
Katie Pierce earned 1336 total points
ID: 40416652
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.
Book1---Split-Combine.xlsx
0
 
LVL 7

Accepted Solution

by:
Katie Pierce earned 1336 total points
ID: 40416658
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
0
 

Author Closing Comment

by:agwalsh
ID: 40421253
This works but would have liked a little more explanation of the substitute function as mentioned by ProfessorJimJam ..thanks.
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40421285
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.
0
 

Author Comment

by:agwalsh
ID: 40421308
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.
0
 

Author Comment

by:agwalsh
ID: 40423465
Great stuff. Thanks :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question