Excel 2007 : Separate Column Information into Others

**bw1** · 01-12-2010, 01:39 PM

I have a large spreadsheet that one column "I" has fax, cell and pager numbers. I need to relocate the cells that have fax numbers in them into a separate column. What formula would I use for this task. The work fax is always on the right side and the numbers range from local 7 digits to international. Here are some examples.

5555555 fax
5555555555 fax
15555555555 fax

Any help would be appreciated.

**NBVC** · 01-12-2010, 02:03 PM

=If(Isnumber(search("fax",A2)),trim(substitute(a2,"fax","")),"")

this looks at A2 and if it contains "fax" it extracts the number out, if not, it leaves cell blank.

**bw1** · 01-12-2010, 02:19 PM

Originally Posted by NBVC

=If(Isnumber(search("fax",A2)),trim(substitute(a2,"fax","")),"")

this looks at A2 and if it contains "fax" it extracts the number out, if not, it leaves cell blank.

Thank you -- that worked great!

**NBVC** · 01-12-2010, 02:44 PM

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save