Can anyone help with a formula to separate a field containing numbers and
letters into numbers per below? I only know how to use for eg =left(X,x) but
that isn't helpful as different fields have different number of numeric
charcters.


123ABC - 123
1234DEF - 1234
12GH - 12
--
ACCAguy

--
ACCAguy

Based on your samples:

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:255"))))

Leading 0s will be stripped off.

0123ABC will return 123

--
Biff
Microsoft Excel MVP


"ACCAguy" <(E-Mail Removed)> wrote in message
news:2BB03CFE-57A4-4FA9-8716-(E-Mail Removed)...
> Can anyone help with a formula to separate a field containing numbers and
> letters into numbers per below? I only know how to use for eg =left(X,x)
> but
> that isn't helpful as different fields have different number of numeric
> charcters.
>
>
> 123ABC - 123
> 1234DEF - 1234
> 12GH - 12
> --
> ACCAguy
>
> --
> ACCAguy

Copy the data
Open Msword and paste

CTRl+F
Findwhat= ^$
Replace=Blank
Replace All


Hardeep kanwar

"ACCAguy" wrote:

> Can anyone help with a formula to separate a field containing numbers and
> letters into numbers per below? I only know how to use for eg =left(X,x) but
> that isn't helpful as different fields have different number of numeric
> charcters.
>
>
> 123ABC - 123
> 1234DEF - 1234
> 12GH - 12
> --
> ACCAguy
>
> --
> ACCAguy

If **all** the digits in your field will **always** be in front of any
non-digits (as your examples showed), then this formula will retrieve
them...

=LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW($1:$9),1))))

Here, the formula assumes there will never be more than 9 digits in the
field; if there can be, then change the $9 to $99 (or, if you want, to a $
sign followed by that maximum number of possible digits). Note that this
formula returns text, which means leading zeroes will be preserved. If you
want a number returned instead (which means you don't care about leading
zeroes), then you can use this instead...

=--LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW($1:$9),1))))

--
Rick (MVP - Excel)


"ACCAguy" <(E-Mail Removed)> wrote in message
news:2BB03CFE-57A4-4FA9-8716-(E-Mail Removed)...
> Can anyone help with a formula to separate a field containing numbers and
> letters into numbers per below? I only know how to use for eg =left(X,x)
> but
> that isn't helpful as different fields have different number of numeric
> charcters.
>
>
> 123ABC - 123
> 1234DEF - 1234
> 12GH - 12
> --
> ACCAguy
>
> --
> ACCAguy

"T. Valko" <biffinp...@comcast.net> wrote...
>Based on your samples:
>
>=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:255"))))
....

If the OP's cell values always begin with numerals, when would your
MIN(FIND(...)) ever return anything other than 1? If that's
representative of the OP's data, use a shorter formula.

=LOOKUP(1E+300,--LEFT(A4,seq))

where seq is a name defined as

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

Better to define its $1:$65536 range reference specific to a worksheet
that contains only unchanging cells.

As for other kinds of cell contents, if A1 contained xy12E-10ab, would
the OP want 12 or 0.0000000012 as the returned value? Your formula
returns the latter. Similar problems if periods or commas follow the
first set of numerals. Your formula returns the leftmost longest
substring that could be converted into a number, not the leftmost
longest string of decimal numerals. It's also fail if the leftmost
longest string of numerals had length 16 or more. Better to return
strings, then allow the OP to decide whether or not to convert them
into numbers.

To return only the leftmost longest string of decimal numerals, try
the array formula

=LEFT(MID(A1,MATCH(1,--ISNUMBER(-MID(A1,seq,1)),0),255),
MATCH(0,-ISNUMBER(-MID(MID(A1,MATCH(1,--ISNUMBER(-MID(A1,seq,1)),0),
255),seq,1)),0)-1)

If you need to do this often, consider using Laurent Longre's
MOREFUNC.XLL add-in, which provides a function named REGEX.MID which
could be used as follows.

=REGEX.MID(A1,"\d+",1)

Now a plug for OpenOffice Calc, which provides simplified regular
expressions. This could be done using

=MID(LEFT(A1;SEARCH("[^0-9]";A1;SEARCH("[0-9]";A1))-1);SEARCH("[0-9]";A1);
255)

Excel may be wonderful for numerical calculations, but its text
functions are mired in mid-1980s Lotus 123 Release 2.x equivalent
functionality. Other than supporting longer strings and adding the
useless BAHTTEXT and nearly useless CLEAN functions, there have been
NO changes in Excel's text functions (improvements, increased
functionality, just plain more of 'em) since version 3. Pathetic!
Note: Word provides support for simple regular expressions, so someone
on at least one of the Office development teams knows something about
how to implement them, but maybe the Excel team suffers from NIH.

smartin <smartin...@gmail.com> wrote...
....
>[OT]
....
>Yours is the first post I have noticed in this group where OpenOffice
>Calc was mentioned. Do you have an article comparing OOC and Excel?

Only some other newsgroup responses.

Here in very brief with no claims of completeness of even
comprehensiveness.

1. OOo Calc provides regular expressions in SOME worksheet functions
and Edit>Find/Replace. Excel provides nothing close.

2. OOo Calc worksheet references in 3D range references can be
relative or absolute. Excel worksheet references are always absolute.

3. OOo Calc provides a FORMULA function which returns the formula in
the referenced cell. Excel provides the XLM function GET.CELL which
can do the same, among many other things, but it can't be used
directly in cell formulas standard worksheets.

4. OOo Calc accepts more than 30 arguments and more than 7 levels of
function call nesting.

5. OOo Calc's ROW and COLUMN functions NEVER return arrays, which is a
real PITA, so formulas like

=LOOKUP(1E300;1/(A1:A100>1000);ROW(A1:A100))

to return the row number of the LAST cell in A1:A100 that's greater
than 1000 require references to ranges containing sequential integers
in OOo Calc.

6. More generally but without details, OOo Calc can handle only a
subset of Excel array formulas.

7. OOo Calc is A LOT SLOWER than Excel when recalculating large
workbooks.

8. OOo has poor documentation. Excel's online help has its flaws,
including some outright errors that haven't been corrected in over a
decade, but it's much more complete.

9. OOo Calc and Excel 2003 and prior provide usable UIs. Excel 2007
provides the dog's lunch UI. The greatest favor Microsoft has ever
done for a competitor is placing restrictions in the lincensing terms
of the effluent UI that prohibit its use in products competing
directly against Office.

>If the OP's cell values always begin with numerals,
>when would your MIN(FIND(...)) ever return
>anything other than 1?

Good point. I used the first thing that came to mind thus the generic
"extract numbers" formula.

--
Biff
Microsoft Excel MVP


"Harlan Grove" <(E-Mail Removed)> wrote in message
news:98fa14f7-e32d-4e7f-b1b0-(E-Mail Removed)...
> "T. Valko" <biffinp...@comcast.net> wrote...
>>Based on your samples:
>>
>>=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:255"))))
> ...
>
> If the OP's cell values always begin with numerals, when would your
> MIN(FIND(...)) ever return anything other than 1? If that's
> representative of the OP's data, use a shorter formula.
>
> =LOOKUP(1E+300,--LEFT(A4,seq))
>
> where seq is a name defined as
>
> =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))
>
> Better to define its $1:$65536 range reference specific to a worksheet
> that contains only unchanging cells.
>
> As for other kinds of cell contents, if A1 contained xy12E-10ab, would
> the OP want 12 or 0.0000000012 as the returned value? Your formula
> returns the latter. Similar problems if periods or commas follow the
> first set of numerals. Your formula returns the leftmost longest
> substring that could be converted into a number, not the leftmost
> longest string of decimal numerals. It's also fail if the leftmost
> longest string of numerals had length 16 or more. Better to return
> strings, then allow the OP to decide whether or not to convert them
> into numbers.
>
> To return only the leftmost longest string of decimal numerals, try
> the array formula
>
> =LEFT(MID(A1,MATCH(1,--ISNUMBER(-MID(A1,seq,1)),0),255),
> MATCH(0,-ISNUMBER(-MID(MID(A1,MATCH(1,--ISNUMBER(-MID(A1,seq,1)),0),
> 255),seq,1)),0)-1)
>
> If you need to do this often, consider using Laurent Longre's
> MOREFUNC.XLL add-in, which provides a function named REGEX.MID which
> could be used as follows.
>
> =REGEX.MID(A1,"\d+",1)
>
> Now a plug for OpenOffice Calc, which provides simplified regular
> expressions. This could be done using
>
> =MID(LEFT(A1;SEARCH("[^0-9]";A1;SEARCH("[0-9]";A1))-1);SEARCH("[0-9]";A1);
> 255)
>
> Excel may be wonderful for numerical calculations, but its text
> functions are mired in mid-1980s Lotus 123 Release 2.x equivalent
> functionality. Other than supporting longer strings and adding the
> useless BAHTTEXT and nearly useless CLEAN functions, there have been
> NO changes in Excel's text functions (improvements, increased
> functionality, just plain more of 'em) since version 3. Pathetic!
> Note: Word provides support for simple regular expressions, so someone
> on at least one of the Office development teams knows something about
> how to implement them, but maybe the Excel team suffers from NIH.

Hi,

If you have typed in abc123 in cell A1, then enter the following array
formula (confirmed by Ctrl+Shift+Enter) in cell B1:

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$18),1)),0),COUNT(1*MID(A1,ROW($1:$18),1))+IF(ISNUMBER(MATCH(".",MID(A1,ROW($1:$18),1),0)),1,0))

Regards,

Ashish Mathur
Excel MVP
www.ashishmathur.com

"ACCAguy" <(E-Mail Removed)> wrote in message
news:2BB03CFE-57A4-4FA9-8716-(E-Mail Removed)...
> Can anyone help with a formula to separate a field containing numbers and
> letters into numbers per below? I only know how to use for eg =left(X,x)
> but
> that isn't helpful as different fields have different number of numeric
> charcters.
>
>
> 123ABC - 123
> 1234DEF - 1234
> 12GH - 12
> --
> ACCAguy
>
> --
> ACCAguy