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,R OW(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(A 1)))),1)+1)*1

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

--
Regards,
Tom Ogilvy


Ed wrote in message
...
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.

"Tom Ogilvy" wrote...
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.
....

Alternatives,

http://groups.google.com/groups?thre...ws.indigo. ie

Ed wrote:
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.

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


On Sat, 7 Feb 2004 17:07:18 -0500, "Ed" wrote:

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.

Ed

A caveat with this code.

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

Gord

On Sat, 07 Feb 2004 16:30:12 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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


On Sat, 7 Feb 2004 17:07:18 -0500, "Ed" wrote:

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.

"Gord Dibben" <gorddibbATshawDOTca wrote...
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.

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Ed" wrote in message
...
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.

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

In article ,
says...
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.

"Tushar Mehta" wrote...
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" wrote...
....
and to pull the last numeral substring,

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

Mucked that up. Make that

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

In article ,
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 ,
says...
"Tushar Mehta" wrote...
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")

"Tushar Mehta" wrote...
...
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.