extract numbers from a alphanumeric cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Igneshwara reddy - 27 Feb 2007 17:23 GMT

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.

Reply to this Message

vezerid - 27 Feb 2007 17:36 GMT

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

On Feb 27, 8:23 pm, Igneshwara reddy <Igneshwara
r...@discussions.microsoft.com> wrote:

> 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.

Reply to this Message

Ron Coderre - 27 Feb 2007 17:40 GMT

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

> 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.

Reply to this Message

Ron Rosenfeld - 27 Feb 2007 17:55 GMT

>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.

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

Reply to this Message

Bob Phillips - 27 Feb 2007 18:24 GMT

=--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},""))))

Signature

---
HTH

Bob

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

> 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.

Reply to this Message