Link to home
Start Free TrialLog in
Avatar of SihleIns
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-###-####
Avatar of redmondb
redmondb
Flag of Afghanistan image

Hi, 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.
Avatar of SihleIns
SihleIns

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?
ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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, SihleIns.
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.
Hi, SihleIns.

Please see attached.

Regards,
Brian.Tidy-Phone-V2.xlsx
Works Perfect!  Thanks for updating the formula to remove the 0.
Glad to help, SihleIns.