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. |
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. |
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. |
extract numbers from a alphanumeric cell
|
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. |
All times are GMT +1. The time now is 09:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com