Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Thanks a lot to both, it is exactly what i wanted,
Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parsing in Excel? | Excel Worksheet Functions | |||
Parsing when deliminator is a string | Excel Worksheet Functions | |||
Parsing Data w/ a Formula (another question) | Excel Worksheet Functions | |||
Parsing Data with Formulas (vs Text-to-Columns) | Excel Worksheet Functions | |||
Parsing text in Excel | Excel Worksheet Functions |