extracting numerics from literal strings

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

If my cell A1 contains a string such as "Total 30 employees", how do I
extract the value (e.g. 30) from this string and put it in cell B1?

Thanks.
 
Array enter (ctlr+Shift+enter rather than just enter in B1)
=MID(A1,SMALL(IF(ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"",ROW(IND
IRECT("1:"&LEN(A1)))),1),LARGE(IF(ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1)))
,1)*1),"",ROW(INDIRECT("1:"&LEN(A1)))),1)-SMALL(IF(ISERROR(MID(A1,ROW(INDIRE
CT("1:"&LEN(A1))),1)*1),"",ROW(INDIRECT("1:"&LEN(A1)))),1)+1)*1

is one way, but I am sure there is a better way.
 
Tom Ogilvy said:
Array enter (ctlr+Shift+enter rather than just enter in B1)
=MID(A1,SMALL(IF(ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"",
ROW(INDIRECT("1:"&LEN(A1)))),1),LARGE(IF(ISERROR(MID(A1,ROW(
INDIRECT("1:"&LEN(A1))),1)*1),"",ROW(INDIRECT("1:"&LEN(A1)))),1)
-SMALL(IF(ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"",
ROW(INDIRECT("1:"&LEN(A1)))),1)+1)*1

is one way, but I am sure there is a better way.
Click to expand...
....

Alternatives,

http://groups.google.com/[email protected]
 
Ed said:
If my cell A1 contains a string such as "Total 30 employees", how do I
extract the value (e.g. 30) from this string and put it in cell B1?

Thanks.
Click to expand...

Hi Ed
one way:
=MID(A1,MIN(IF(ISNUMBER(VALUE(MID(A1,seq,1))),seq,1024)),MAX(IF(ISNUMBE
R(VALUE(MID(A1,seq,1))),seq,0))-MIN(IF(ISNUMBER(VALUE(MID(A1,seq,1))),s
eq,1024))+1)
entered as array formula (CTRL+SHIFT+ENTER)
and seq as defined name with the formula: =ROW(INDIRECT("1:1024))

HTH
Frank
 
Ed

Programming method.

Sub Move_Nums()

Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Offset(0, 1).Value = strTemp
Next rngR
End Sub

Gord Dibben Excel MVP
 
Ed

A caveat with this code.

Make sure you have an empty column to the right of your data.

Gord

Ed

Programming method.

Sub Move_Nums()

Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Offset(0, 1).Value = strTemp
Next rngR
End Sub

Gord Dibben Excel MVP


If my cell A1 contains a string such as "Total 30 employees", how do I
extract the value (e.g. 30) from this string and put it in cell B1?

Thanks.
Click to expand...
Click to expand...
 
Gord Dibben said:
A caveat with this code.

Make sure you have an empty column to the right of your data. ....
Gord Dibben <gorddibbATshawDOTca> wrote: ....
Click to expand...
....

More of a caveat needed. Because you're offsetting from rngR rather than
rngRR or (better but still not safe) Selection, there's no guarantee that
this code would overwrite cells within Selection, possibly within rngRR. For
example, select A1:E5 with all cell containing "foo" except C3 containing
"foo123bar". The macro would happily overwrite D3 with 123 even though all
of column F may be empty. Better, perhaps, to provide udfs that would allow
the user/OP to select where to put the result.
 
Couple of things about this routine:

1. It requires A1 contains one and only one numeric value.
2. Decimal points will work, but use 0.1 instead of .1

Sub test()
Dim strTemp As String

strTemp = Range("A1").Value
Do Until IsNumeric(Left(strTemp, 1)): strTemp = Mid(strTemp, 2): Loop
Do Until IsNumeric(Right(strTemp, 1)): strTemp = Mid(strTemp, 1,
Len(strTemp) - 1): Loop
Range("B1").Value = strTemp
End Sub
 
Yet another alternative...

If you install the functions at
http://www.tmehta.com/regexp/add_code.htm, and if the string in
question is in column A, you could use the formula

=RegExpSubstitute(A1,"[^\d]*","")

The results will look like:

total 30 employees 30
total 30 emp87loyees 3087

If you have multiple numbers and want only the first set, use
=RegExpSubstitute(A3,"(^[^\d]*)(\d*)(.*$)","$2")

This will yield:

total 30 employees 30
total 30 emplo87yees 30
t12otal 30 emplo87yees 12


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar Mehta said:
Yet another alternative...

If you install the functions at
http://www.tmehta.com/regexp/add_code.htm, and if the string in
question is in column A, you could use the formula

=RegExpSubstitute(A1,"[^\d]*","")
Click to expand...
....

While this seems to work, \D it the better/safer/more Perl-compatible way to
express the complement of the character class represented by \d. It also
does more work than it should.

=RegExpSubstitute(A1,"\D+","")

would be optimal in terms of regexp performance. [Tangent: participate in
comp.lang.perl.misc and you'll really learn how to use regular expressions.]
If you have multiple numbers and want only the first set, use
=RegExpSubstitute(A3,"(^[^\d]*)(\d*)(.*$)","$2")
Click to expand...

Better to use

=RegExpSubstitute(A3,"^\D*(\d+).*$","$1")

and to pull the last numeral substring,

=RegExpSubstitute(A3,"^.*(\d+)\D*$","$1")

and to pull the n_th numeral substring counting left to right,

=RegExpSubstitute(A3,"^(\D*(\d+)){"&(n-1)&"}\D*(\d+).*$","$3")
 
Harlan Grove said:
and to pull the last numeral substring,

=RegExpSubstitute(A3,"^.*(\d+)\D*$","$1")
Click to expand...
....

Mucked that up. Make that

=RegExpSubstitute(A3,"^.*?(\d+)\D*$","$1")
 
would be optimal in terms of regexp performance. [Tangent: participate in
comp.lang.perl.misc and you'll really learn how to use regular expressions.]
Click to expand...
Yeah, I probably should do that. At the same time, it would be far
more useful to convince people to use regular expresssions with XL in
the first place. <g>

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Tushar Mehta said:
Yet another alternative...

If you install the functions at
http://www.tmehta.com/regexp/add_code.htm, and if the string in
question is in column A, you could use the formula

=RegExpSubstitute(A1,"[^\d]*","")
Click to expand...
...

While this seems to work, \D it the better/safer/more Perl-compatible way to
express the complement of the character class represented by \d. It also
does more work than it should.

=RegExpSubstitute(A1,"\D+","")

would be optimal in terms of regexp performance. [Tangent: participate in
comp.lang.perl.misc and you'll really learn how to use regular expressions.]
If you have multiple numbers and want only the first set, use
=RegExpSubstitute(A3,"(^[^\d]*)(\d*)(.*$)","$2")
Click to expand...

Better to use

=RegExpSubstitute(A3,"^\D*(\d+).*$","$1")

and to pull the last numeral substring,

=RegExpSubstitute(A3,"^.*(\d+)\D*$","$1")

and to pull the n_th numeral substring counting left to right,

=RegExpSubstitute(A3,"^(\D*(\d+)){"&(n-1)&"}\D*(\d+).*$","$3")
Click to expand...
 
...
...
Yeah, I probably should do that. At the same time, it would be far
more useful to convince people to use regular expresssions with XL in
the first place. <g>
Click to expand...
...

Begging the question whether it'd be useful to try to convince Microsoft to add
regular expressions to Excel or at least VBA since they already exist in
VBScript. But we all know Microsoft's willingness to add *useful* features to
Excel. How much you wanna bet we'll get a flashing text format before regexps?
 
Back
Top