How do I copy numeric section of cell from alpha-numeric cell

A

ACCAguy

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
 
T

T. Valko

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
 
H

Hardeep_kanwar

Copy the data
Open Msword and paste

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


Hardeep kanwar
 
R

Rick Rothstein

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))))
 
H

Harlan Grove

T. Valko said:
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"))))
Click to expand...
....

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.
 
S

smartin

Harlan said:
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)
Click to expand...

[OT]

Harlan,

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?
 
H

Harlan Grove

smartin said:
[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?
Click to expand...

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.
 
T

T. Valko

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

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

--
Biff
Microsoft Excel MVP


Harlan Grove said:
T. Valko said:
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"))))
Click to expand...
...

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.
Click to expand...
 
A

Ashish Mathur

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can anyone help - I am sure there is a easy way to do this 6
how do i use format cells to alpha / numeric values 2
Ignore Leading Zeros in Mixed Alpha-Numeric Text Fields Select Query 5
How do I restrict entry into a cell to only alpha/numeric? 3
How do I assign a numeric value to a letter 2
Sorting Alpha numeric data and keeping the number sequence 5
remove alpha or non-numeric characters from cell 8
How do I change column addresses from numeric to alpha? 1

Top