Find and render text

[David P.]

I need a formula that will isolate cell phone numbers in two different cells
(A1 & B1). Please see the example below to explain this: I thought the best
way to do that was to find what is in betwee "/ " & " CE" (as in the 1st
example) or between the beginning of the cell and " CE" (2nd example). I
always put " CE" after a cell phone number as a standard. I likewise always
use "/ " everytime I'm seperating two numbers. There are cases where there
will be no "/ " since there is only one number. Here's the idea:

340-8688 / 818-444-6122 CE (This would be A1)
775-8844 (This would be B1)

Or in some cases I may have it like this with no "/ " and no area code for
example:

818-6122 CE (cell A1)
775-8844 (cell B1)

I need to render the cell phone only i.e. 818-444-6122 (1st example) or
818-6122 (2nd example).

Many thanks.

 

[Max]

One thought is to tinker with Data > Text to Columns
On a spare copy of your data sheet, select the column data range, then click
Data > Text to Columns > Check "Delimited" > Next > Check "Space" > Finish.
This will isolate all data fragments into its own cell/col. Clean up by
selecting the "unwanted" portions, right-click on these (in turn) > Delete >
Shift cells left > OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[David P.]

Thank you for your suggestion Max. My intent is to isolate the phone # into a
seperate cell. I've already built the spreadsheet format and all cells around
the area where the customers info is entered is already taken up that's why I
was hoping for a smart formula that could find it for me(so long as I stay
within the parameters of using "/ " & " CE") without moving to a cell out of
the flow of where the rest of the info is being entered. Your shot at it
appreciated if that is within the scope of what you do. Many thanks.

 

[Max]

Assuming source data in A1 down eg:
340-8688 / 818-444-6122 CE
818-6122 CE
etc

Try in say, B1, copy down:
=IF(ISERROR(SEARCH("/",A1)),TRIM(SUBSTITUTE(A1,"CE","")),
TRIM(SUBSTITUTE(MID(A1,SEARCH("/",A1)+1,99),"CE","")))

This derives it in col B as:
818-444-6122
818-6122

Works ok? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik