extracting numerics from literal strings
Ed
extract the value (e.g. 30) from this string and put it in cell B1?
Thanks.
Tom Ogilvy
=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.
--
Regards,
Tom Ogilvy
Ed <eddie...@hotmail.com> wrote in message
news:#XozAdc7...@TK2MSFTNGP10.phx.gbl...
Harlan Grove
>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),"",
>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.
Alternatives,
http://groups.google.com/groups?threadm=F2fT9.1857%24V6.2445%40news.indigo.ie
Frank Kabel
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
Gord Dibben
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
Gord Dibben
A caveat with this code.
Make sure you have an empty column to the right of your data.
Gord
Harlan Grove
>A caveat with this code.
>
>Make sure you have an empty column to the right of your data.
>Gord Dibben <gorddibbATshawDOTca> wrote:
...
>>Sub Move_Nums()
...
>> Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
>> xlTextValues)
>> For Each rngR In rngRR
>> rngR.Offset(0, 1).Value = strTemp
...
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.
Rob van Gelder
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
--
Rob van Gelder - http://www.vangelder.co.nz/excel
"Ed" <eddie...@hotmail.com> wrote in message
news:%23XozAdc...@TK2MSFTNGP10.phx.gbl...
Tushar Mehta
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
In article <#XozAdc7...@TK2MSFTNGP10.phx.gbl>,
eddie...@hotmail.com says...
Harlan Grove
>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]*","")
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")
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
...
>and to pull the last numeral substring,
>
>=RegExpSubstitute(A3,"^.*(\d+)\D*$","$1")
Mucked that up. Make that
=RegExpSubstitute(A3,"^.*?(\d+)\D*$","$1")
Tushar Mehta
says...
> 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.]
>
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
In article <GnyVb.2339$_4....@www.newsranger.com>, hrl...@aol.com
says...
Harlan Grove
..
>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>
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?
--
To top-post is human, to bottom-post and snip is sublime.