Replace or move specific value with numbers series

Question

I'm looking for a specific script do move or replace some specific value's in difference cells.

I show your a example, maybe it's more clear.

I have a lot of this cells:

moving barn 1990/01
rebuild house 1990/212
moving house 1992/23 * changing
rebuild 54 barn 92/12

Like this as result.

Now I want to move the year/number to another cell. Or recplace the value with only the year/number. But the year/number is not always on the end of the cell. And the year/number, number is not always 2 digits.

I'm looking for some script that can find the following things: (x = number) xxxx/xx xxxx/xxx

And that moved to another cell.

Hopefully do you understand what I mean and can you help me with this problem.

In Excel, all data is entered on a cell-by-cell basis, so it is impossible to determine whether your data has the data presented in one cell or is separated. There is also a lot of data, but it is difficult to judge what it is like. Inserting the image so that the cell address appears will help you get the answer. — Dy.Lee, Mar 01 '21 at 15:03

score 0 · Answer 1 · answered Mar 01 '21 at 15:37

I recommend regExp. See here for all of this.

Sub RegexReplace()

    Dim objRegExp As Object
    Dim colMatches As Object
    Dim s As String
    Dim vDB As Variant, vR() As Variant
    Dim r As Long, i As Long
    
    Set objRegExp = CreateObject("VBscript.RegExp")
    objRegExp.Pattern = "[0-9]{1,4}/[0-9]{1,3}"
    objRegExp.IgnoreCase = True
    objRegExp.Global = False
    
    vDB = Range("a1", Range("a" & Rows.Count).End(xlUp))
    r = UBound(vDB, 1)
    
    ReDim vR(1 To r, 1 To 1)
    For i = 1 To r
        s = vDB(i, 1)
        If objRegExp.Test(s) Then
           Set colMatches = objRegExp.Execute(s)
           vR(i, 1) = colMatches.item(0)
        End If
    Next i
    Range("c1").Resize(r, 1) = vR
End Sub

When your data is in column a as follows, extract the data in column c.

Wow! Good job! Is is also possible to move the result instead of copy? Example that in A1 after the script 'only moving barn' is? — Jelle van Soelen, Mar 02 '21 at 10:38

Replace or move specific value with numbers series

1 Answers1