Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract numbers from a alphanumeric cell
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract numbers from a alphanumeric cell
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 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract numbers from a alphanumeric cell
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&"0 123456789")),ROW($1:$10000)))) Using your posted examples fdsfa24655 fd123fds the formula returns: 24655 and 123 Does that help? *********** Regards, Ron XL2002, WinXP "Igneshwara reddy" 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract numbers from a alphanumeric cell
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract numbers from a alphanumeric cell
=--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),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) "Igneshwara reddy" <Igneshwara wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to extract decimal numbers e.g. $1.57 from alphanumeric string | Excel Discussion (Misc queries) | |||
Extract only numbers from an alphanumeric field in Excel? | Excel Discussion (Misc queries) | |||
how to extract decimal numbers from alphanumeric strings in Excel | Excel Discussion (Misc queries) | |||
Function to extract numbers from an alphanumeric cell | Excel Worksheet Functions | |||
separating numbers and letters from alphanumeric cell contents | Excel Worksheet Functions |