Copy data from one column to another

Currently I have an excel column that may contain one off, all values or none of the values below:

Home: xxx-xxx-xxxx Office: xxx-xxx-xxx Cell: xxx-xxx-xxx Fax: xxx-xxx-xxx

I need to copy telephone number found in this column to dedicated column without the word Home, Office, Cell or Fax

Home: xxx-xxx-xxxx to 'HOMEPHONE' column
Office: xxx-xxx-xxx to "OFFICEPHONE' column
Cell: xxx-xxx-xxx to 'CELLPHONE' column
Fax: xxx-xxx-xxx to 'FAX' column

to make things even harder, some data may not even exist or may have different format, for example:

Home: Office: xxx-xxx-xxx Cell: xxx-xxx-xxx Fax: xxx-xxx-xxx
Office: xxx-xxx-xxx Cell: xxx-xxx-xxx Fax: xxx-xxx-xxx
Cell: xxx-xxx-xxx Fax: xxx-xxx-xxx
Home xxx-xxx-xxxx Office xxx-xxx-xxx Cell xxx-xxx-xxx Fax xxx-xxx-xxx
Home xxx xxx xxxx Office: xxx-xxx-xxx Cell: xxx-xxx-xxx Fax: xxx-xxx-xxx

Any way to tackle this?

I was thinking to possibly search for:
HOME or HOME: and next 12 digits, however if it contains word OFFICE OR FAX or CELL ignore it
and so on.

There are hundreds of thousands entries and manually updating is not an option...

Thanks for suggestions.

2by4

Sample file attached with expected output.

test excel sheet.xlsx

This formula will get you the homephone output in the format you list in your example:

=IF(LEFT(E2,4)="Home",IF(LEFT(TRIM(E2),8)="Home: Of","",SUBSTITUTE(MID(TRIM(E2),IF(ISNUMBER(SEARCH("home:",TRIM(E2))),7,6),12)," ","-")),"")

Hope this helps.

Pete

Argh, for the most part it worked great when entries contained full data in correct format, except that some entries are incomplete or have no space between 'HOME:xxx'

CURRENT DATA:
Home:123-123-1111 Office: Emergency:123-123-5555

When I run this command above, I get:

OUTPUT:
23-123-1111-

CURRENT DATA:
Home: 123- Office: Emergency:

OUTPUT:
123--Office:

Looking through hundreds of thousands of lines it's hard to see every single error.

Well, you have now introduced some other variations - the word "Emergency:", which may or may not be followed by a phone number, and variable-length phone numbers.

I think you would be better using some helper columns to try to get the data into a standard format (e.g. with a <colon><space> after each keyword), and then applying simpler formulae to the output from those helpers.

It would be better still to control the format of the data in the first place.

Hope this helps.

Pete

Well, I thought by opening separate thread I'd be doing good, apparently not as I was told it's the same thing as in this thread... Anyway, I've spent some time normalizing fields. I understand it would have been better to start with correct formatting, but I'm trying to migrate from this old solution to a new product and need to extract as much data as I can in correct fields.
I need some help finishing normalizing data in following example below.I understand it would have been better to start with correct formatting, but I'm trying to migrate from this old solution to a new product and need to extract as much data as I can in correct fields.

I need help to figure out how to:

1. To get rid off any text if there is e.g. "OFFICE: Jamie 11111111" to make "OFFICE: 11111111", or "OFFICE: no phone number" to "OFFICE: " or "EMERGENCY: Jenny 1111111111" to "EMERGENCY: 1111111111". I do NOT care about "Some random text" because final goal is to extract data after HOME: OFFICE: CELL: EMERGENCY:
2. If there is any 7 digit number, I need to add 000 to beginning of number, e.g. "OFFICE: 1111111" to "OFFICE: 0001111111"
3. If there is any 3 digit long number, delete it.

HOME: 1111111111 OFFICE: no phone number
HOME: 1111111111 OFFICE: 1111111
HOME: 1111111111 OFFICE: 1111111111 EMERGENCY: 1111111111
HOME: 1111111111 OFFICE: EMERGENCY: 1111111111
HOME: OFFICE: EMERGENCY: 1111111111 CELL: 1111111111
HOME: 1111111 OFFICE: 1111111111 EMERGENCY: Jenny 1111111111 CELL: 1111111111
HOME: 1111111111 CELL: 1111111 OFFICE: 1111111111 EMERGENCY: Julie 1111111111
HOME: 1111111111 OFFICE: 1111111111 Some random text HOME: 1111111111 OFFICE: EMERGENCY:
HOME: 000 OFFICE: 1111111111 EMERGENCY: Tara 1111111111 CELL: 1111111111
HOME: 1111111111 Some random text OFFICE: 1111111111 HOME: 1111111111 OFFICE: EMERGENCY:

thanks for help in advance

Can you attach that data as an Excel file, so we can see what your starting point is.

Pete

Thank you for all the help, I am really awed by help I'm getting in this forum!

Here is the latest set of data I've been provided with after another team had extracted from the old program.

I am not sure if normalizing data should be done first, or it can be updated/ignored during while parsing.

example.xlsx

Thanks again for help!