Solved

importing multiple word names into Excel

Posted on 2014-10-31
10
109 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
10 Comments
 
LVL 25

Expert Comment

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

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 166 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
 
LVL 7

Assisted Solution

by:Katie Pierce
Katie Pierce earned 334 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Accepted Solution

by:
Katie Pierce earned 334 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 25

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now