Parsing 124A into 124
Hello:
I am looking for a way to parse the examples below in excel: 124 A into 124 12345 into 12345 55/49 into 55 888b into 888 Does such function exist in excel or any idea how I could implement it in VBA? The longest serie of digits before a non numeric caracters is what I am looking for. Thanks a lot, Chris |
How about:
=--LEFT(A1,MATCH(FALSE, ISNUMBER(-MID(A1&"x",ROW(INDIRECT("1:"&LEN(A1)+1)),1)),0)-1) (all one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) It'll provide an error if there are no leading digits (like: ABC1234). Christophe wrote: Hello: I am looking for a way to parse the examples below in excel: 124 A into 124 12345 into 12345 55/49 into 55 888b into 888 Does such function exist in excel or any idea how I could implement it in VBA? The longest serie of digits before a non numeric caracters is what I am looking for. Thanks a lot, Chris -- Dave Peterson |
On 27 Sep 2005 03:32:19 -0700, "Christophe"
wrote: Hello: I am looking for a way to parse the examples below in excel: 124 A into 124 12345 into 12345 55/49 into 55 888b into 888 Does such function exist in excel or any idea how I could implement it in VBA? The longest serie of digits before a non numeric caracters is what I am looking for. Thanks a lot, Chris Array formula: =IF(ISNUMBER(A1),A1,LEFT(A1,MATCH(FALSE,ISNUMBER( -MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)) To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. The above returns the number as a text string. If you require it to be an actual number, prefix the formula with a double unary: =--IF(ISNUMBER(A1),A1,LEFT(A1,MATCH(FALSE,ISNUMBER( -MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)) Note that the formulas assume your "number" ends with the first NON-numeric character. --ron |
Thanks a lot to both, it is exactly what i wanted,
Chris |
All times are GMT +1. The time now is 02:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com