How to split cells into 2 in Excel 2000

  • Thread starter Thread starter Patrick
  • Start date Start date
Here's something I'm trying to do, perhaps it is also similar to wha
original poster wants:

I have cells with data that needs to be separated, example:

1234 Main St. Jamaica, NY 11432 718-222-2222

The address, city, state, zip and phone number need to be automaticall
put into their own separate fields.

1. Can someone offer a formula to deal with this type of situation?

2. Is there one formula that works with other types of data, such a
first name, last name, birthdate, again that need to be separated fro
one cell to many.

3. Can this process be automated with macro, if so, how?

Thank you
 
Have you tried Data - Text to Column - fixed width
double click on the vertical lines that you want to remove.
 
1. Can someone offer a formula ...

Assuming that "1234 Main St. Jamaica, NY 11432 718-222-2222" in Cell A1,
B1: =LEFT(A1,FIND(",",A1)-1)
C1: =MID(A1,FIND(",",A1)+2,FIND(" ",A1,FIND(",",A1)+2)-FIND(",",A1)-2)
D1: =MID(A1,FIND(" ",A1,FIND(",",A1)+2)+1,FIND(" ",A1,FIND(" ",A1,FIND(",",A1)+2)+2)-FIND(" ",A1,FIND(",",A1)+2)-1)
E1: =MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(",",A1)+2)+2)+1,20)
 
Thanks for the awesome reply!! My data has changed slightly. Here i
an example of a cell that I need to manipulate:

A2: 'ANONYMOUS CORP.,858-210-3021

In this case, the phone number and the name need to be separated int
two cells. Also, the ' and , marks need to be removed.

AD2: Vista Ca 92083-7642

In this case, the city, state and zip need to be separated int
separate fields. Thi is easy with text to column feature easy becaus
there are spaces between the data. However, when I use space t
delimit the city,state,zip, it works ONLY if the city is one name. I
it is two names (such as New York), then it puts each word in its ow
cell.

The data is laid out in one worksheet in excel, each entry starts in
and stretches across, with each cell having separate data. Only in th
case of two cells (A and AD) is there data like the examples above.

I used the text to columns feature, in the first example, however whe
I parse the data, some extra characters are left in, namely a
character next to the phone number (example: '888-222-2222)

Do you have an equation or a way to refine this?

Thanks!
 
Back
Top