Extract numbers from text

Hi
I have a cell with a sentence in text and numbers in between. Is there a
formular so that if I write the formular in B1, it will give me all the
numbers or give me only the text og A1 ?
Thanks
--
Therese

Here are a couple of User Defined Functions you can use.

Public Function PullText(rngTemp As Range)
Dim strTemp As String

For i = 1 To Len(rngTemp.Value)
If (Asc(Mid(rngTemp.Value, i, 1)) <= 47 Or
Asc(Mid(rngTemp.Value, i, 1)) >= 58) Then
strTemp = strTemp & Mid(rngTemp.Value, i, 1)
End If
Next i
PullText = strTemp
End Function

Public Function PullNumbers(rngTemp As Range)
Dim strTemp As String

For i = 1 To Len(rngTemp.Value)
If (Asc(Mid(rngTemp.Value, i, 1)) >= 48 And
Asc(Mid(rngTemp.Value, i, 1)) <= 57) Then
strTemp = strTemp & Mid(rngTemp.Value, i, 1)
End If
Next i
PullNumbers = strTemp
End Function

hHi Kletcko
Thanks a lot for you help, I will see if I can use it, and ten efter that I
will go back to "general questions" where I belong. A bit too tough for me,
but thanks for your time.
--
Therese


"Kletcho" skrev:

> Here are a couple of User Defined Functions you can use.
>
> Public Function PullText(rngTemp As Range)
> Dim strTemp As String
>
> For i = 1 To Len(rngTemp.Value)
> If (Asc(Mid(rngTemp.Value, i, 1)) <= 47 Or
> Asc(Mid(rngTemp.Value, i, 1)) >= 58) Then
> strTemp = strTemp & Mid(rngTemp.Value, i, 1)
> End If
> Next i
> PullText = strTemp
> End Function
>
> Public Function PullNumbers(rngTemp As Range)
> Dim strTemp As String
>
> For i = 1 To Len(rngTemp.Value)
> If (Asc(Mid(rngTemp.Value, i, 1)) >= 48 And
> Asc(Mid(rngTemp.Value, i, 1)) <= 57) Then
> strTemp = strTemp & Mid(rngTemp.Value, i, 1)
> End If
> Next i
> PullNumbers = strTemp
> End Function
>
>

You would copy these into a module in your workbook (alt-F11 to get to
the visual basic editor and then Insert - Module to get a module.
Paste the code in the white area.)

Then in a cell in your workbook you would refer to the formula like any
other default excel formula:

=PullNumbers(A1)

or

=PullText(A1)

On Tue, 18 Apr 2006 09:24:02 -0700, Therese <Therese@discussions.microsoft.com>
wrote:

>Hi
>I have a cell with a sentence in text and numbers in between. Is there a
>formular so that if I write the formular in B1, it will give me all the
>numbers or give me only the text og A1 ?
>Thanks

Another option would be to download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr/.

This can be distributed freely with your workbook.

You can then use the regular expression formulas:

=REGEX.SUBSTITUTE(A1,"\D") to extract the numbers and

=REGEX.SUBSTITUTE(A1,"\d") to extract everything that is not a number.

This will work unless your sentence has more than 255 characters. If it does,
similar solutions using VBA regular expressions can be devised.


--ron