extract numbers from a alphanumeric cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How to extract numbers from a alphanumeric cell.

Eg: If a cell contains fdsfa24655, my result should be 24655
If a cell contains fd123fds, my result should be 123.
 
For lack of something simpler...

=MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(1:20),
1)),ROW(1:20))),MAX(IF(ISNUMBER(--MID(A1,ROW(1:20),1)),ROW(1:20)))-
MIN(IF(ISNUMBER(--MID(A1,ROW(1:20),1)),ROW(1:20)))+1)

In the above formula we assume that the cell with the mixed number is
in A1. We also assume that there will be no more than 20 characters.
Replace A1 and 1:20 with whatever cell and whatever range.

This is an *array* formula. You must commit it with Ctrl+Shift+Enter.

HTH
Kostis Vezerides
 
With
A1: containing alphanumeric text which contains a consecutive number string

Try this:
B1:
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Using your posted examples fdsfa24655 fd123fds
the formula returns: 24655 and 123

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
On Tue, 27 Feb 2007 09:23:00 -0800, Igneshwara reddy <Igneshwara
How to extract numbers from a alphanumeric cell.

Eg: If a cell contains fdsfa24655, my result should be 24655
If a cell contains fd123fds, my result should be 123.
Click to expand...

This assumes your numbers are sequential within the string:

Define a name, seq, (per Harlan Grove) as

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$65536,255,1))

Insert/Name/Define
Names in Workbook: seq
Refers To: (the above formula)

Then, with, for example, your cell = A1, use this **array** formula:

=MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,seq,1)),0),
MATCH(FALSE,ISNUMBER(-MID(MID(A1,1+MATCH(
TRUE,ISNUMBER(-MID(A1,seq,1)),0),255),seq,1)),0))

To enter an array formula, after putting the formula into the formula bar, hold
down <ctrl><shift> while hitting <enter>. Excel will place braces {...} around
the formula.


--ron
 
=--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top