Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formula to analize info in a cell
I have a cell that has a mix a text and numbers. I need to know how many of
the charaters are text and how many are numbers. It usually is something like JDR89765 or JD5674, meaning that the text is on the left side. If possible I would like to read from left to right and get a count of how many text char there are before the first number. Any help will be greatly appreciated. Thanks in advance. |
#2
|
|||
|
|||
=LEN(A1)-SUMPRODUCT(--ISNUMBER(-RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1)))))
will tell you the number of text characters in the cell "Jordan" wrote in message ... I have a cell that has a mix a text and numbers. I need to know how many of the charaters are text and how many are numbers. It usually is something like JDR89765 or JD5674, meaning that the text is on the left side. If possible I would like to read from left to right and get a count of how many text char there are before the first number. Any help will be greatly appreciated. Thanks in advance. |
#3
|
|||
|
|||
One way, assume the string is in A1
=MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1)),0) entered with ctrl + shift & enter, and to get the number of of numbers just use the cell result, assume you put that formula in B1, in C use =LEN(A1)-B1 note that if there are spaces they will be counted so you could wrap each cell refof in the cell with TRIM =MATCH(TRUE,ISNUMBER(--MID(TRIM(A1),ROW(INDIRECT("1:"&LEN(TRIM(A1))))+1,1 )),0) then =TRIM(LEN(A1)-B1 for the numbers Regards, Peo Sjoblom "Jordan" wrote: I have a cell that has a mix a text and numbers. I need to know how many of the charaters are text and how many are numbers. It usually is something like JDR89765 or JD5674, meaning that the text is on the left side. If possible I would like to read from left to right and get a count of how many text char there are before the first number. Any help will be greatly appreciated. Thanks in advance. |
#4
|
|||
|
|||
N Harkawat wrote:
=LEN(A1)-SUMPRODUCT(--ISNUMBER(-RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))) will tell you the number of text characters in the cell .... Not reliably. If A1 contained JDR89765X, your formula returns 9 rather than 4. An alternative that works no matter how decimal numerals and other characters are scrambled is =SUMPRODUCT(LEN(SUBSTITUTE(G2,{0;1;2;3;4;5;6;7;8;9 },"")))-9*LEN(G2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
Display answer only in another cell of one containing a formula | Excel Discussion (Misc queries) | |||
looking for a formula | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
How do I do math on a cell name in formula? | Excel Worksheet Functions |