SihleIns

asked on

# Move phone number from column B to Column AS and leave other data in cell behind

I have an Excel spreadsheet with text and phone numbers in column B. I'm using the following formula to find the phone numbers and display them in column AS.

=MID(B2,SEARCH("???-???-????",B2),12)

The problem I'm having is that I need to REMOVE the phone numbers from column B after copying them to Column AS and leave the other data behind. I can't seem to find any method to accomplish this.

Here is an example of what the data looks like in column B: (The phone number is not always at the end of the data string)

Example Company Management 407-###-####

=MID(B2,SEARCH("???-???-??

The problem I'm having is that I need to REMOVE the phone numbers from column B after copying them to Column AS and leave the other data behind. I can't seem to find any method to accomplish this.

Here is an example of what the data looks like in column B: (The phone number is not always at the end of the data string)

Example Company Management 407-###-####

ASKER

I'm ok with copy and PasteSpecial values for column AS.

The formula you provided is giving me the data without the phone numbers in column AT. However, not all rows have a phone number in them so the formula you provided shows #VALUE! when a phone number is not provided and that now presents me with another problem of trying to combine the cleaned rows with the rows that were already correct.

If it were possible to move the phone numbers to column AS with one formula rather than showing the copied value from my original formula that would make this process a lot more simple.

Any thoughts?

The formula you provided is giving me the data without the phone numbers in column AT. However, not all rows have a phone number in them so the formula you provided shows #VALUE! when a phone number is not provided and that now presents me with another problem of trying to combine the cleaned rows with the rows that were already correct.

If it were possible to move the phone numbers to column AS with one formula rather than showing the copied value from my original formula that would make this process a lot more simple.

Any thoughts?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

I'm using Excel 2010. What you provided works! My problem was the original formula I used to copy the phone numbers into column AS displayed #VALUE! if a phone number was not present. The formula you provided did what it was supposed to do and compared the data in column AS with B the #VALUE! was causing the problem. Once I removed the #VALUE! from the AS column the data from column B was copied to the correct cell.

Thanks for your help.

Thanks for your help.

Thanks, SihleIns.

ASKER

I have one more question about this formula. Is there any way to get it to display a blank cell if there is nothing in column B and AS ? Currently if both cells are blank it display's 0. Having a 0 in the cell will cause problems later on with this spreadsheet.

Thanks again.

Thanks again.

ASKER

Works Perfect! Thanks for updating the formula to remove the 0.

Glad to help, SihleIns.

How about the following in AT2...

=TRIM(SUBSTITUTE(B2,AS2,""

If you actually want to update B2 itself then you'll either have to do Copy and PasteSpecial or a macro. What's your preference?

Regards,

Brian.