Only numbers formula

[PapaDos]

An array formula (Ctrl-Shift_Enter) solution is possible:

=MID( LEFT( A1, MAX( ISNUMBER( -MID( A1 & "_", ROW($1:$50), 1 ) ) * NOT(
ISNUMBER( -MID( A1 & "_", ROW($2:$51), 1 ) ) ) * ROW($1:$50) ) ), MAX( NOT(
ISNUMBER( -MID( "_" & A1, ROW($1:$50), 1 ) ) ) * ISNUMBER( -MID( "_" & A1,
ROW($2:$51), 1 ) ) * ROW($1:$50) ), 999999 )

But it is not reliable if you insert or delete rows.
This one is safer but slower because it is "volatile":

=MID( LEFT( A1, MAX( ISNUMBER( -MID( A1 & "_", ROW( INDIRECT( "$1:$50" )) ,
1 ) ) * NOT( ISNUMBER( -MID( A1 & "_", ROW( INDIRECT( "$2:$51" ) ), 1 ) ) ) *
ROW( INDIRECT( "$1:$50" ) ) ) ), MAX( NOT( ISNUMBER( -MID( "_" & A1, ROW(
INDIRECT( "$1:$50" ) ), 1 ) ) ) * ISNUMBER( -MID( "_" & A1, ROW( INDIRECT(
"$2:$51" ) ), 1 ) ) * ROW( INDIRECT( "$1:$50" ) ) ), 999999 )

They are good for up to 50 characters strings (it can be adjusted as needed).
They both return a #VALUE! error if the string contains no number...

--

Regards,
Luc.

"Festina Lente"

 

[Sandy Mann]

Hi PapaDos,

I got so involved in solving the problem that I forgoit that rtequirement as
well

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[PapaDos]

LOL

Simply including my formulas in a RIGHT(..., 7) function should do the trick.
The second one may then hit the nested limitations of Excel, replacing the
NOT(ISNUMBER(...)) parts with ISERROR(...) would be necessary...

 

[Harlan Grove]

An array formula (Ctrl-Shift_Enter) solution is possible: ....
This one is safer but slower because it is "volatile":

=MID( LEFT( A1, MAX( ISNUMBER( -MID( A1 & "_",
ROW( INDIRECT( "$1:$50" )) , 1 ) ) * NOT( ISNUMBER( -MID( A1 & "_",
ROW( INDIRECT( "$2:$51" ) ), 1 ) ) )
* ROW( INDIRECT( "$1:$50" ) ) ) ),
MAX( NOT( ISNUMBER( -MID( "_" & A1,
ROW( INDIRECT( "$1:$50" ) ), 1 ) ) )
* ISNUMBER( -MID( "_" & A1,
ROW( INDIRECT( "$2:$51" ) ), 1 ) )
* ROW( INDIRECT( "$1:$50" ) ) ), 999999 )

Sometimes additional whitespace adds clarity. To me, this is an
instance where too much added whitespace reduces clarity.

Also, there are ways to eliminate the volatility. E.g., define a name
like seq referring to the formula

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

Then use it in the NON-ARRAY formula

=LOOKUP(
2,
1/ISNUMBER(
-RIGHT(
LEFT(
A1,
LOOKUP(
2,
1/ISNUMBER( -MID(A1,seq,1) ),
seq
)
),
{1,2,3,4,5,6,7}
)
),
RIGHT(
LEFT(
A1,
LOOKUP(
2,
1/ISNUMBER( -MID(A1,seq,1) ),
seq
)
),
{1,2,3,4,5,6,7}
)
)

or without undue whitespace

=LOOKUP(2,1/ISNUMBER(-RIGHT(LEFT(A1,LOOKUP(2,1/ISNUMBER(-MID(A1,
seq,1)),seq)),{1,2,3,4,5,6,7})),RIGHT(LEFT(A1,LOOKUP(2,
1/ISNUMBER(-MID(A1,seq,1)),seq)),{1,2,3,4,5,6,7}))

If you add a blank worksheet, name it something you're unlikely to use
for other worksheets, e.g., ' ' (a single space - single quotes only
used to delimit it), define seq with that worksheet active, then hide
that worksheet, seq will be effectively nonvolatile.

 

[Harlan Grove]

Not sure what you want to do if there are no digits in the string.
The UDF below will return a #NUM! error in that case, but you could
modify that. ....
Option Explicit
Function LastDigits(str As String)
'Requires setting reference to Microsoft VBScript Regular
'Expressions 5.5
Dim oRegex As RegExp
Dim mc As MatchCollection
Const sPattern As String = "\d{1,7}"

Set oRegex = New RegExp
oRegex.Pattern = sPattern
oRegex.Global = True

If oRegex.Test(str) = True Then
Set mc = oRegex.Execute(str)
LastDigits = CDbl(mc(mc.Count - 1))
Else
LastDigits = CVErr(xlErrNum)
End If

End Function

An alternative approach, replace everything except the desired
substring with nothing. And return "" if no numeric substrings. And
FTSUHOI, make it general.


Function resubst( _
s As String, _
mp As String, _
rp As String, _
Optional GL As Boolean = True, _
Optional IC As Boolean = True, _
Optional ML As Boolean = True _
) As String
'---------------------------------
'requires ref to Microsoft VBScript Regular Expressions 5.5
Dim re As New RegExp

re.Global = GL
re.IgnoreCase = IC
re.MultiLine = ML
re.Pattern = mp

resubst = re.Replace(s, rp)
End Function


and use it as

=resubst(A1,"^.*?(\d{1,7})?\D*$","$1")

 

[Ron Rosenfeld]

An alternative approach, replace everything except the desired
substring with nothing. And return "" if no numeric substrings. And
FTSUHOI, make it general.


Function resubst( _
s As String, _
mp As String, _
rp As String, _
Optional GL As Boolean = True, _
Optional IC As Boolean = True, _
Optional ML As Boolean = True _
) As String
'---------------------------------
'requires ref to Microsoft VBScript Regular Expressions 5.5
Dim re As New RegExp

re.Global = GL
re.IgnoreCase = IC
re.MultiLine = ML
re.Pattern = mp

resubst = re.Replace(s, rp)
End Function


and use it as

=resubst(A1,"^.*?(\d{1,7})?\D*$","$1")

I like that, also.
--ron