• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

separating text within one cell

maradykstra

New Member
Hello,

I want to separate text that is all one one cell. I'm familiar with the text to column with the delimiters and fixed width. The fixed width works very well, but not all text is lined up appropriately. An example of a text is:


Smith, Carol 3334 S. Camelot Way, SLC, UT 84012 (I want to separate the names from the addresses)


I have a list of similar text in one but different data. I can highlight the whole column and do the fixed width but spacing isn't distributed evenly so when I do the fixed width it'll cut some of the first names out. Also, when I do the delimiters, it's way too many cells afterwards so that's why the fixed width works but only one cell at a time.


Is there a faster way?


Thanks.
 
Maradykstra

Is there a way to configure the source of where the data is coming from to ensure that it has a consistant delimeter ie "," between fields

This would enable you to easily use the Text to Columns as you have described
 
Thanks Hui. I'll ask the person who gave me the list to configure the list so it's easier.


Thanks again for your help. How did you get so good in Excel?
 
Hi,


Try this in B1 copied down,


=SUBSTITUTE(A1," "," ") this substitutes a single space for say 10 spaces, then copy and paste values, then do a text to columns using space as the delimiter
 
Hi,


An other option using formulae would be to extract the name using a selection of the LEFT(), RIGHT(), LEN() and FIND() formulae.


Chandoo's post below covers this topic:

http://chandoo.org/wp/2010/01/19/usernames-from-email-formulas/


Myles
 
so, picking up on Clarity's post, you would add 2 columns -- one to hold the name, and one for the address. So let's presume your combined name + address is in column C.


Column A will hold your names, Column B the addresses


In A, use this
Code:
=LEFT(C1,FIND(" ",C1,FIND(",",C1)+2))

In B, use this =RIGHT(C1,LEN(C1)-LEN(A1))


Paste A & B down


Note: this presumes you don't have something like a 'middle initial' that shows up on some records and not others.
 
Back
Top