How can I remove non-numeric characters in cells and concatenate result to URL?

Question

I have a bunch of cells with string like this: WFM 1601

And this: WFM 2231, WFM 2402

And this too: Campaign 1680, 2402, 2784

I used code, below, to split the string in a single cell into multiple columns (max of 3).

Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next

lRow = Range("U" & Rows.Count).End(xlUp).Row
Set MyRows = Range("U19:U" & lRow)
For Each cell In MyRows

splitVals = Split(cell.Value, ",")
    totalVals = UBound(splitVals)
    Range(Cells(cell.Row, ActiveCell.Column + 1), Cells(cell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
Next

Now, I'm trying to figure out a way to get rid of all NON numeric characters and leave only numbers. Then, concatenate these numbers, which are all IDs for processes in a SharePoint site that I work with, so I want to place the URL for each number, at the end of a static string, and next to the number that was just split into separate columns.

Here is a screen shot.

I have Column U, and I want to generate Column V to Column AA.

I can extract only numbers using the function below.

Function GetNums(target As Range)
    Dim MyStr As String, i As Integer
    MyStr = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    If target.Value = "None" Then GoTo GoNone
    For i = 1 To Len(target.Value)
        If IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)
    Next i
    GoTo GoExit
GoNone:
    GetNums = "None"
    Exit Function
GoExit:
    GetNums = MyStr

End Function

However, this won't meet the requirement as it checks all characters in a cell, and just turns this: WFM 2231, WFM 2402 . . . Into this: 22312402 I really need some way to distinguish the two IDs: 2231 2402

Answer 1

I would use Regular Expressions to extract the number groups. If it turns out there are other criteria for what constitutes a valid digit sequence, that would be easier to implement by changing the regex.

Here's an example with your original Data in Column A of the active sheet.

---

Option Explicit
Sub CreateURL()
    Dim RE As Object, MC As Object, M As Object
    Const sPat As String = "\b\d+\b" 'whole words that are all digits
    Const sBaseURL As String = "htpps://collaborate.process...&ID="
    Dim I As Long, J As Long
    Dim rSrc As Range, C As Range

'This will be on active sheet
'Suggest you specify actual worksheet
Set rSrc = Range(Cells(1, 1), Cells(Rows.Count, "A").End(xlUp))

Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = sPat
    .Global = True
End With

For Each C In rSrc
    If RE.test(C.Text) = True Then
        Set MC = RE.Execute(C.Text)
        J = -1
        For Each M In MC
            J = J + 2
            C.Offset(0, J) = M
            C.Offset(0, J + 1) = sBaseURL & M
        Next M
    End If
Next C

End Sub

---

And here's the results of running this macro against data in column A:

Here is a formal explanation of the Regex, with links to more detail that hopefully still work:

\b\d+\b

\b\d+\b

Options: Case insensitive; ^$ match at line breaks

• Assert position at a word boundary (position preceded or followed—but not both—by an ASCII letter, digit, or underscore) \b
• Match a single character that is a “digit” (ASCII 0–9 only) \d+
  • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
• Assert position at a word boundary (position preceded or followed—but not both—by an ASCII letter, digit, or underscore) \b

Created with RegexBuddy

Answer 2

I can help for the 1st part, to check if a value is numeric or not.

You did the split. Now, you can check if the variables you get are numeric or not. Example :

We want to check if the value in A1 is numeric :

isnum = isNumeric(range("A1"))

isnum is true if the value in A1 is numeric, else it is false.