Separate text from numbers

you could extract the numbers see attached will do 3 numbers

If you want a simple formula and the ability to extract as many individual numbers as there may be, you could add a custom function to your worksheet. It would evaluate the entire text string and pull out all numeric values.

If your text string were in A1, then this formula in B1:

=NUMS(A1)
..would result in 4089, 4111

Or:
=NUMS(A1, "-")
...would result in 4089-4111 by using the optional second parameter to use a custom delimiter.

Here's the UDF I have for this:

Option Explicit

Function NUMS(RNG As Range, Optional Delim As String) As String
If RNG.Cells.Count > 1 Then
    NUMS = "1 cell only"
    Exit Function
End If

If Delim = "" Then Delim = ", "
Dim buf As String, i As Long, MyArr
buf = RNG

MyArr = Array("-", ",", "&")    'add more replacement characters here

For i = LBound(MyArr) To UBound(MyArr)
    buf = Replace(buf, MyArr(i), " ")
Next i

MyArr = Split(Trim(buf), " ")
buf = ""
    For i = LBound(MyArr) To UBound(MyArr)
        If IsNumeric(MyArr(i)) Then buf = buf & Delim & MyArr(i)
    Next i

If buf = "" Then
    NUMS = "none"
Else
    NUMS = Mid(buf, Len(Delim) + 1, Len(buf))
End If
End Function

I highlighted in red the only part of the code you might need to edit, it's the characters to search for in the string and replace with spaces... so far just a dash, comma and ampersand.

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.

@JB

this was very helful and detailed..thanks

however i am getteing value "none" in B if the number and text in A are joined
private4089,trailer4111

can this be fixed ?
many thanks

Please don't quote previous posts for no reason, it just clutters the thread making it hard to read. Use the QUICK REPLY box below.

Why are you changing your text string syntax now? The first was simple and usable. Are you changing it now for some good reason? If not, don't join the text and numerics that way. The UDF works and merging the text that way means the whole thing would need to be redone in a far less efficient way.

legolas, given it is your intention to split the numbers I confess I'm not sure why you chose to ignore Martin's example ?
The functions provided would extract the numbers to B & C automatically - for either syntax.

If Martin and I have taken care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

how many are you likely to need?
here is a (not very efficient udf )that will extract all numbers into one cell .then copy paste special values then split with text to columns
use as =remove_text(a1) and drag down

Function Remove_text(Rng As String) As String
Dim Tmp As String
Dim i As Integer
Dim x As Integer


Tmp = Rng
For i = 1 To 47
Tmp = Application.Substitute(Tmp, Chr(i), " ")
Next i
For x = 58 To 255
Tmp = Application.Substitute(Tmp, Chr(x), " ")

Next x
Tmp = Application.Trim(Tmp)

Remove_text = Tmp
End Function

FWIW - if you opted for UDF - Regular Expressions may be worth considering...

Function NumberExtract(rngString As Range, Optional lngInstance As Long = 1) As Variant
Dim RegExp As Object, RegExpMatch As Object
On Error Resume Next
Set RegExp = CreateObject("vbscript.regexp")
With RegExp
    .Global = True
    .IgnoreCase = True
    .Pattern = "[0-9]+"
End With
Set RegExpMatch = RegExp.Execute(rngString)
If lngInstance > RegExpMatch.Count Then
    NumberExtract = ""
Else
    NumberExtract = RegExpMatch(lngInstance - 1)
End If
Set RegExpMatch = Nothing
Set RegExp = Nothing
End Function

Assume string in A1, numbers to go in B1, C1 etc...

B1: =NUMBEREXTRACT($A1,COLUMNS($B1:B1))
copied across

The second variable is optional - ie should you only be looking to return a single numeric value (first) it could be omitted - in this case we increment the value to return 1st, 2nd, 3rd etc...

Not very effiicient though doing this with functions though IMO...a sub routine would be better.

EDIT:

if you want the output for finds to be numeric adjust the 2nd NumberExtract line to:

NumberExtract = Val(RegExpMatch(lngInstance - 1))