Thread: How to replace any 7 digit number at a random position in a cell

Hey there,


I tried to solve my problem by going through an endless number of existing threads but unfortunately nothing I found was quite what I was looking for.


THE PROBLEM:
I have a table with cells that contain mostly text in varying length. Somewhere in those cells, every now and then, in the middle of all the text there is one or more 7 digit numbers. I need to replace these numbers with XXXXXXX. Sounds simple, right? But I have no idea how...


I am sure it is somehow possible with the right combination of IF, MID, LEFT, LOOKUP etc but I just couldn't it figure out.


HELP!

eskimo,

Welcome to the forum!

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

If you're looking for just a few specific sets of numbers, you can use Ctrl+H. If not, it might help to post a dummy workbook letting us know your exact layout. Would the numbers always be 7 digits?

eskimo,

Attached is a modified version of you sample workbook. It contains a button named "Scrub Numbers". That button is assigned to the following macro:

Sub tgr()
    
    Dim rngText As Range
    Dim arrText As Variant
    Dim arrTxtPart As Variant
    Dim TextIndex As Long
    Dim PartIndex As Long
    
    Set rngText = Range("A2", Cells(Rows.Count, "A").End(xlUp))
    arrText = Application.Transpose(rngText.Value)
    
    For TextIndex = 1 To UBound(arrText)
        arrTxtPart = Split(arrText(TextIndex), " ")
        For PartIndex = 0 To UBound(arrTxtPart)
            If Len(arrTxtPart(PartIndex)) = 7 And IsNumeric(arrTxtPart(PartIndex)) Then arrText(TextIndex) = Replace(arrText(TextIndex), arrTxtPart(PartIndex), "xxxxxxx")
        Next PartIndex
    Next TextIndex
    
    rngText.Value = Application.Transpose(arrText)
    
End Sub

To view macros in a workbook, use keyboard shortcut Alt+F11. The macro will take all 7-digit numbers in column A starting in row 2 (I used row 1 for the header) and replace them with "xxxxxxx"

try more func addin then use something like
=REGEX.SUBSTITUTE(A1," \d{7} "," xxxxxxx ")

http://download.cnet.com/Morefunc/30...-10423159.html

eskimo,

The add-in martindwilson suggested should be able to accomodate what you're looking for. If you want to stick with the macro, I made a slight change to compensate for the comma issue. As for the 255 character limit, I'm not sure what's causing that

Sub tgr()
    
    Dim rngText As Range
    Dim arrText As Variant
    Dim arrTxtPart As Variant
    Dim TextIndex As Long
    Dim PartIndex As Long
    
    Set rngText = Range("A2", Cells(Rows.Count, "A").End(xlUp))
    arrText = Application.Transpose(rngText.Value)
    
    For TextIndex = 1 To UBound(arrText)
        arrTxtPart = Split(Replace(arrText(TextIndex), ",", ""), " ")
        For PartIndex = 0 To UBound(arrTxtPart)
            If Len(arrTxtPart(PartIndex)) = 7 And IsNumeric(arrTxtPart(PartIndex)) Then arrText(TextIndex) = Replace(arrText(TextIndex), arrTxtPart(PartIndex), "xxxxxxx")
        Next PartIndex
    Next TextIndex
    
    rngText.Value = Application.Transpose(arrText)
    
End Sub