Stripping text from numbers in a cell

[Michael M]

Hi All
I did a search in the archives for a way to strip all occurences of text
from a cell of numbers, eg,
A3F4DR5V1, would end up being 3451.
I found a post from Bob Phillips ( thanks Bob),with the following formula:

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("4:"&LEN(A1)))))

but it doesn't work, I've tried using CSE but I still get a #N/A error.
Can anybody please tell me where it's not working.
I can do it with code but would rather keep the OP away from macros.

Regards
Michael M

 

[T. Valko]

That formula won't do what you think it will do. It will extract *a* number
from a string but only the first number it finds.

Rewritten and array entered:

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

Will return 3 because 3 is the first number in your string: A3F4DR5V1

If your string was: A354DR5V1, then the result would be 354.

AFAIK there is no single formula that will extract *all numbers randomly
dispersed* from a string.

 

[Ron Rosenfeld]

Hi All
I did a search in the archives for a way to strip all occurences of text
from a cell of numbers, eg,
A3F4DR5V1, would end up being 3451.
I found a post from Bob Phillips ( thanks Bob),with the following formula:

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("4:"&LEN(A1)))))

but it doesn't work, I've tried using CSE but I still get a #N/A error.
Can anybody please tell me where it's not working.
I can do it with code but would rather keep the OP away from macros.

Regards
Michael M

Here is a UDF that will do what you describe:

===================================================
Option Explicit

Function ExtrNums(str As String)
Dim oRegex As Object
Const sPattern As String = "\D"
Dim i As Long

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.Pattern = sPattern

ExtrNums = oRegex.Replace(str, "")
If IsNumeric(ExtrNums) Then ExtrNums = CDbl(ExtrNums)

End Function
======================================================




--ron

 

[Michael M]

Thanks gents
The formula actually works if the number string is concurrent, but that
didn't suit.
I do have a macro already ( thanks anyway Ron), but I was trying to avoid
using it.
There are security issues with macros in my org.......can't trust the
workers, you know !!
Regards and thank you.
Michael M

 

[Harlan Grove]

I did a search in the archives for a way to strip all occurences of text
from a cell of numbers, eg,
A3F4DR5V1, would end up being 3451.

....

If there wouldn't be more than 15 decimal numerals in these cells, you could
try the array formula

=SUM(IF(ISNUMBER(1/MID(A1,seq,1)),MID(A1,seq,1)
*10^MMULT(-(seq<TRANSPOSE(seq)),-ISNUMBER(1/MID(A1,seq,1)))))

where seq is defined as

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

However, you'd be better off downloading and installing Laurent Longre's
MOREFUNC.XLL add-in, available from

http://xcell05.free.fr/english/

and use its REGEX.SUBSTITUTE add-on function in formulas like

=REGEX.SUBSTITUTE(A1,"\D+")

This is nearly the same as Ron Rosenfeld's udf, but allows for greater
generality/flexibility. [And Ron should have learned by now to use \D+
rather than just \D when performing global replacements.]

Note: the array formula will return a number, while the latter formula will
return a text string.

 

[Harlan Grove]

If there wouldn't be more than 15 decimal numerals in these cells, you
could try the array formula

=SUM(IF(ISNUMBER(1/MID(A1,seq,1)),MID(A1,seq,1)
*10^MMULT(-(seq<TRANSPOSE(seq)),-ISNUMBER(1/MID(A1,seq,1)))))

....

Not quite. It skips 0s. Change it to

=SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)
*10^MMULT(-(seq<TRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))

 

[Michael M]

Thanks Harlan
I'll give it a try.

Regards
michael M

 

[T. Valko]

...

Not quite. It skips 0s. Change it to

=SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)
*10^MMULT(-(seq<TRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))

Wow! That's pretty slick.

I notice it doesn't handle decimals but for what it does it is quite an
accomplishment and relatively compact.

 

[Ron Rosenfeld]

However, you'd be better off downloading and installing Laurent Longre's
MOREFUNC.XLL add-in

Harlan,

I can't seem to access his web site this morning -- receiving a

"Forbidden
"You don't have permission to access /forums/viewforum.php on this server.
"Apache/ProXad [May 15 2007 17:32:33] Server at xcell05.free.fr Port 80"


Do you know if he has updated morefunc to work with Excel 07?

A month or so ago, there were some issues posted here with some of the
functions. And I don't have Excel07 so could not confirm it.

On another note, does D+ work faster than D in regex implementations?
--ron

 

[CLR]

ASAP Utilities, a free add=in available at www.asap-utilities.com includes a
feature that will do what you want.

Vaya con Dios,
Chuck, CABGx3

 

[Harlan Grove]

I can't seem to access his web site this morning -- receiving a

"Forbidden
"You don't have permission to access /forums/viewforum.php on this server.
"Apache/ProXad [May 15 2007 17:32:33] Server at xcell05.free.fr Port 80"

Same for me. Hopefully just a glitch that'll be fixed in a few hours.

Do you know if he has updated morefunc to work with Excel 07?

....

No idea.

On another note, does D+ work faster than D in regex implementations?

You mean \D+ faster than \D? Yes, generally. If there were no sequences of
multiple non-decimal digit characters in the string, the former might be
slightly slower due to overhead for the + closure, but if there were any
multiple character sequences, + closure would process them more quickly than
repeatedly processing each character. At least that's how it works in
scripting languages.