forginganewone
Board Regular
- Joined
- Mar 14, 2018
- Messages
- 64
Example cell values :
<tbody>
</tbody>
I want a formula which will extract only numbers from a cell but it should start counting numbers after 0.
For example : from this Demo-PAT-DIRBS-05513-C , it should extract 5513.
I tried using below formulas :
=RIGHT($C4,LEN($C4)-(SUM((MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9},$C4)),FIND({1,2,3,4,5,6,7,8,9},$C4)))-1))))
^ this failed because in some cells the number value was in mid.
{=SUMPRODUCT(MID(0&C2,LARGE(INDEX(ISNUMBER(--MID(C2,ROW($1:$999),1))* ROW($1:$999),0),ROW($1:$999))+1,1)*10^ROW($1:$999)/10)}
^ this is giving #Num Error
Demo-PAT-DIRBS-05513-C
<tbody> </tbody> |
<tbody>
</tbody>
I want a formula which will extract only numbers from a cell but it should start counting numbers after 0.
For example : from this Demo-PAT-DIRBS-05513-C , it should extract 5513.
I tried using below formulas :
=RIGHT($C4,LEN($C4)-(SUM((MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9},$C4)),FIND({1,2,3,4,5,6,7,8,9},$C4)))-1))))
^ this failed because in some cells the number value was in mid.
{=SUMPRODUCT(MID(0&C2,LARGE(INDEX(ISNUMBER(--MID(C2,ROW($1:$999),1))* ROW($1:$999),0),ROW($1:$999))+1,1)*10^ROW($1:$999)/10)}
^ this is giving #Num Error
Last edited: