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. |
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. |
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. |
All times are GMT +1. The time now is 12:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com