Solved

importing multiple word names into Excel

Posted on 2014-10-31
10
111 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cascading dropdown 9 29
Struggling to Lock and grey out cells using VBA. Help please! 8 27
Excel macro runs twice 13 51
remove upper case characters in excel 4 21
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

867 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

24 Experts available now in Live!

Get 1:1 Help Now