?
Solved

importing multiple word names into Excel

Posted on 2014-10-31
10
Medium Priority
?
125 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
10 Comments
 
LVL 26

Expert Comment

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

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
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!

 
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 26

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

777 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