4

I need to remove all text from numbers within a cell, then split the two numbers across two cells and be formatted 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

Screen shot1(Before):

There may be 1000s of other lines of these and they will always start in H2. There are occupied cells either side.

If possible the code needs to form part of a bigger macro that has actions before and after so it would be great to be able to copy and paste it into the middle.

Desired Result(After):

Here's a link to a sample doc as I'm not sure how to upload on here - http://www.filedropper.com/sample_13

Community
  • 1
  • 1
stevieb123
  • 57
  • 6

1 Answers1

2

Please run the following steps:

  1. add manually column between H & I which should be empty at the beginning.

  2. add this function to your VBA project:

    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

  3. add this subroutine to your VBA project:

    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

  4. select range of cells in column H which you want to process (tip: select 2-3 at the beginning to understand the idea)

  5. run Run_Function() subroutine...

Tried & Tested for sample data you provided!

Infinite Recursion
  • 6,511
  • 28
  • 39
  • 51
Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
  • 4
    Sorry guys, I'm new to the forum and apologies, I didn't spend enough time reading the rules. I've spent some time creating my own solution which was a text to columns approach and then deleting the unwanted columns. I'll be sure to stick to forum rules in the future by explaining what I've tried. Sorry again. KazJaw, thanks very much for the solution! – stevieb123 Jan 13 '14 at 09:39