Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select only the letters from a cell
Hi,
I have a list of values which include letters and numbers: ADP45 DSVC54 DEPT25 etc.... In the next column I want to only see the letters without the numbers: ADP DSVC DEPT Is there a function to do this? Thanks. Isa. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select only the letters from a cell
Here is one way
=SUBSTITUTE(A1,LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5 ,6,7,8,9},A1&"0123456789")),{1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15})),"") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Isa" wrote in message ... Hi, I have a list of values which include letters and numbers: ADP45 DSVC54 DEPT25 etc.... In the next column I want to only see the letters without the numbers: ADP DSVC DEPT Is there a function to do this? Thanks. Isa. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select only the letters from a cell
This way works - however I'm confused on one item.
The MID(A1,MIN(SEARCH({0,1,2,3,4,5 ,6,7,8,9},A1&"0123456789")),{1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15}) function Searched for the first occurance of a number - got that - I understand that the '0123456789" adds a number just in case the cell has no number. However the {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15} is supposed to give the length of the number (one, two, three, .... positions)? how is that happening - when I'm trying to pull it apart - I'm only getting 1 for this field regardless on how big the number is? "Bob Phillips" wrote: Here is one way =SUBSTITUTE(A1,LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5 ,6,7,8,9},A1&"0123456789")),{1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15})),"") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Isa" wrote in message ... Hi, I have a list of values which include letters and numbers: ADP45 DSVC54 DEPT25 etc.... In the next column I want to only see the letters without the numbers: ADP DSVC DEPT Is there a function to do this? Thanks. Isa. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
enhanced conditional formatting | Excel Discussion (Misc queries) | |||
select a cell and the one next to it | Excel Discussion (Misc queries) | |||
Counting the number of letters in a cell | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
I want to select the first blank cell in column A | Excel Worksheet Functions |