separating data within a cell - how?

[drooth]

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?

MUCH GRATITUDE TO THE RESPONDERS...

 

[Frank Kabel]

Hi
one solution (without VBA and formulas) could be the "Text to columns"
function. Goto 'Data - Text to columns' choose your delimiters (blanks
or spaces). Though depending on your data this could be getting
complicated if you haven't a seperator

HTH
Frank

 

[drooth]

That's a good idea, however sometimes there are no spaces between data.


Also, how could I automate your idea with a macro?

Thanks for the reply!

 

[Frank Kabel]

Hi
To provide you a solution you have to provide more detail:
- How is your data structured (that is how are the components of your
text identified)
- provide some sample data (plain text, no attachment please) which
reflect your data for all different cases
But depending on your data structure there may be no automatic solution
for your problem

HTH
Frank
P.S.: please don't multipost to different groups as this will scatter
your responses.

 

[drooth]

Thank you for your offer to help!

Here is an example of a cell that I need to manipulate:

A2: 'FIVE STAR SERV CORP.,818-260-3021

In this case, the phone number and the name need to be separated into
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 into
separate fields. This might be easy because there are spaces between
the data.

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

THANK YOU FOR ANY HELP WITH THIS PROJECT!

 

[Frank Kabel]

Hi
one way:
0. Save your spreadsheet - work on a backup file
1. For column A
- add some columns left to column A
- Select column A
- goto 'Data - Text to columns' and choose the ',' as delimiter

2. for column AD
- the same as above, just choose Space as delimiter

This will work only if all rows in column A and column AD respectively
have the same data structure

HTH
Frank

 

[drooth]

I was able to do that, however when I parse the data, some extr
characters are left in, namely a ' character next to the phone numbe
(example: '888-222-2222)

Also, when I use space to delimit the city,state,zip, it works ONLY i
the city is one name. If it is two names (such as New York), then i
puts each word in its own cell.

Ideas on how to refine that?

Finally, if I wanted to automate this, shall I just use the macr
recording feature?

Thanks again