Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and render text
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. -- David P. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and render text
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." wrote: 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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and render text
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. -- David P. "Max" wrote: 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." wrote: 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). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and render text
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 --- "David P." wrote: 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. -- David P. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and render text within IF Function | Excel Discussion (Misc queries) | |||
Chart does not render plotted points correctly, data comes from a | Charts and Charting in Excel | |||
Getting Excel to render HTML | Excel Discussion (Misc queries) | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Free pdf creator which can render hyperlinks? | Excel Worksheet Functions |