Find & Replace consecutive numbers in a cell to replace with a single word

Question

How to find and replace 5 consecutive numbers from a cell. In my data sheet, there are several cells that have a string of 5 consecutive numbers. These 5 consecutive numbers are either at the beginning of the cell or towards the middle of the cell. I need a formula that will allow me to find these 5 consecutive numbers from the cells and replace them with the word 'this' Please note, some of the cells have numbers that list measurements (i.e. 27" height x 15" L x 15" W) These I would like to leave as is. I need to find those that ONLY have five consecutive numbers (i.e. 01193 WOOD SHIPSWHEEL CLOCK is a perfect nautical wall decor) and replace the five consecutive numbers (in this case 01193) to replace it with the word 'this' (End result: this WOOD SHIPSWHEEL CLOCK is a perfect nautical wall decor)

Another example: 

Wall decor with great utility. Support your existing wall decor with 13498 Metal GEAR Clock. 

Find & Replace: 13498

End Result:

Wall decor with great utility. Support your existing wall decor with this Metal GEAR Clock.  

Your help is greatly appreciated! Thank you.

Answer 1

Hi.  Just an idea to your excellent code.

It would adjust 6 or more digits, or something that was very tall (ie  12345")

One idea might be to isolate the 5-digit within a word boundary, and not followed by the double quote to represent  inches.  Quotes are not part of a word boundary.

I might factor Global and Pattern out of the loop. 

Here's what I was thinking...

(    [^\u0022] ->  Not the Unicode 22 character, which is Double Quotes)

    With regExp

        .Global = True

        '/ 5 digit word boudary not followed by double quotes

        .Pattern = "\b\d{5}\b[^\u0022]"

        For Each rngCel In Worksheets("Sheet1").UsedRange

            rngCel.Value = .Replace(rngCel.Value, "this ")

        Next rngCel

    End With

Answer 2

If you want a VBA code solution then the following VBA code should do it. However, it tests all cells in the used range on the worksheet so if you want to restrict it to a specific range then please get back to me.

Also if you don't know how to install the code then get back to me.

Sub ReplaceNumerals()

    Dim rngCel As Range

    Dim regExp As Object

    Set regExp = CreateObject("VBScript.RegExp")

    For Each rngCel In Worksheets("Sheet1").UsedRange

        If rngCel.Value <> "" Then Debug.Print rngCel.Value

        With regExp

            .Global = True

            .Pattern = "[0-9]{5}"   'Any character 0-9 total of 5 characters

            rngCel.Value = .Replace(rngCel.Value, "this ")   'Replace any string matching pattern with "this".

        End With

    Next rngCel

End Sub

Answer 3

Thank you both very much! This VBA code worked GREAT!!!