Transforming messy database into clean database
 

I currently have the following data in column A:

Column A:
123-456-7890
123-456-7890 456-789-0123 / FAX: 123-456-9870
123-456-7890;123-654-7890
123-456-7890;123-654-7890FX1234567
0
?
#N/A
123-456-7890 x1234/fax:123-654-7890
123-456-7890/456-789-01234
123-456-7890; FAX:123-456-9870
123-456-7890 ID1234
123-456-7890 (team 1)
123-456-7890;FAX 321-456-7890
123-456-7890 x123
1-123-456-7890 direct 321-456-7890 fax: 123-456-7980
123-456-789 x1; FAX 123-456-9870
123-456-7890

Is there an easy way to transform this data into Column B so that it picks
up the first set of phone numbers, so it reads:

Column B:
123-456-7890
123-456-7890
123-456-7892
123-456-7893
NA
NA
NA
123-456-7897
123-456-7898
123-456-7899
123-456-7900
123-456-7901
123-456-7902
123-456-7903
123-456-7904
123-456-7905
123-456-7906

Thanks very much for your insight.

Transforming messy database into clean database
 

You might have to play about with Data | Text-to-Columns a few times.
Highlight column A then Data | T-t-C and specify <space as delimeter.
If you are not interested in any of the other data, then you could
delete columns B to F. Then repeat, but specify semi-colon as
delimiter. Repeat again with <oblique as delimiter, and continue until
you have exhausted all combinations.

You could then do Find & Replace (CTRL-H) to do some further clean-ups,
eg:

Find What ?
Replace with NA

Find What 1-123
Replace with 123

I'm not sure if you can replace the error message with NA using Find
and Replace - if you have many of these you could apply a filter and
select #NA then overtype NA in the top-most visible cell and copy this
down the visible cells. You could also do this for the 0 entry.

Bit tedious, but I hope this helps.

Pete

Transforming messy database into clean database
 

Sorry, I've just re-read your posting (more carefully this time!). You
were asking if this could be done in column B. If you want to follow my
suggestions, first copy column A to column B then perform the actions
on column B.

Hope this helps further.

Pete

Transforming messy database into clean database
 

Thanks Pete. I was hoping though for a "magic" solution that allowed using
some kind of advanced formulas/functions that would do this. I suppose a
macro built around what you suggested is more in order. Thanks very much.

"Pete" wrote:

Sorry, I've just re-read your posting (more carefully this time!). You
were asking if this could be done in column B. If you want to follow my
suggestions, first copy column A to column B then perform the actions
on column B.

Hope this helps further.

Pete

Transforming messy database into clean database
 

Thanks for getting back - your data is particularly "messy", so a
formula solution is much more difficult.

Pete