Retrieve only numbers from a cell

[cherambane]

Dear All,

I want to know any function to retrieve only numbers from a cell containing alphanumeric values.

Ex:

B1 = KAR000712
B2 = KA01234
B3 = A/C 112245

I want only 000712, 01234, 112245 etc.,


any formula???


Thanks and Regards,

Basheer

 

[anandvarma]

TRY THIS FUNCTION

=EVAL(REGEX.SUBSTITUTE(B1,"A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z|/|"""))

YOU NEED TO DOWN MOREFUNC TO USE THIS

YOU CAN DOWN AT
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

 

[T. Valko]

Dear All,

I want to know any function to retrieve only numbers from a cell containing alphanumeric values.

Ex:

B1 = KAR000712
B2 = KA01234
B3 = A/C 112245

I want only 000712, 01234, 112245 etc.,


any formula???


Thanks and Regards,

Basheer

This assumes that the number string will always be at the end of the string.

Also, in order to retain any leading zeros the strings will have to be extracted as TEXT strings.

Book1
	B	C
1	KAR000712	000712
2	KA01234	01234
3	A/C 112245	112245
Sheet2

Formula entered in C1 and copied down:

=REPLACE(B1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B1&"0123456789"))-1,"")

 

[bosco_yip]

Two shorter ways :

=REPLACE(B1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B1&5^19))-1,"")

or,

=REPLACE(B1,1,MATCH(,INDEX(0*MID(B1,ROW($1:$99),1),),)-1,"")

Regards

 

[LearnMeExcel]

Two shorter ways :

=REPLACE(B1,1,MATCH(,INDEX(0*MID(B1,ROW($1:$99),1),),)-1,"")

Regards

Could i ask please ?
in MATCH you leave the lookup value empty, for what MATCH will search
and the same thing you did in INDEX , You leave The row_num Empty what is the defult value in this case

 

[Aladin Akyurek]

Two shorter ways :

=REPLACE(B1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B1&5^19))-1,"")

or,

=REPLACE(B1,1,MATCH(,INDEX(0*MID(B1,ROW($1:$99),1),),)-1,"")

Regards

They look expensive to me.

 

[mikerickson]

This Name based approach handles letters anywhere in the string.
If your mixed strings are in column A:

Select E1 and define these names.
(The relative referencing makes the active cell critical.)

Name: oneToNCol
RefersTo: =INDEX(Sheet1!E:E, 1, 1):INDEX(Sheet1!E:E, LEN(Sheet1!$A1), 1)

Name: oneToNRow
RefersTo: =INDEX(Sheet1!1:1, 1, 1):INDEX(Sheet1!1:1, 1, LEN(Sheet1!$A1))

Name: subStrings
RefersTo: =MID(Sheet1!$A1, COLUMN(oneToNRow), ROW(oneToNCol))

Then put this CSE formula in C1 and drag down.
=MAX(IFERROR(VALUE(subStrings),0))

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>KAR000712<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>712</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>KA01234<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1234</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF>A/C 112245<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>112245</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF>abc123xyz23t<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>123</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFFFF>ab123xy456.3f<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>456.3</tr>
</table>