Geek Answers Handbook

Removing text around a number using VBA

I need to remove all the text from the number inside the cell, and then split the two numbers into two cells and format them as numbers, not text. The cell contains text / numbers in the following formats:

between 150,000 and 159,999 per annum
between 60 and 65 per hour
between 70.00 and 74.00 per hour

Screenshot 1 (before):

There may be 1000 other lines, and they will always start with H2. There are busy cells on both sides.

If possible, the code should become part of a larger macro that has before and after actions, so it would be great to copy and paste it in the middle.

Desired result (after):

Here's a link to a sample document, as I'm not sure how to download here - http://www.filedropper.com/sample_13

+4
source share
1

:

  • H I, .

  • VBA:

    Public Function GetNthNumberAlternative(sMark As String, iOrder As Integer) As String

'regexp declaration
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.regexp")

With objRegExp
    .Global = True
    .Pattern = "\d+[.,]\d+|\d+"
        GetNthNumberAlternative = .Execute(sMark)(iOrder - 1).Value

End With

End Function

  • VBA:

    Sub Run_Function()

Dim Cell As Range, tmpText As String
For Each Cell In Selection.Cells
    tmpText = Cell.Value
    Cell = GetNthNumberAlternative(tmpText, 1)
    Cell.Offset(0, 1) = GetNthNumberAlternative(tmpText, 2)
Next Cell
End Sub

  • H, (: 2-3 , )

  • Run_Function() ...

, !

+2

More articles:


All Articles