Forum Discussion

statejj16's avatar
statejj16
Copper Contributor
Feb 12, 2022

Find and Replace ### ### with just ###

What I am looking at is a column consisting of numbers with seven characters (###_L##, where "L" is a letter, # is a number, and "_" is just a space). However, there are sometimes numbers that are (###_###). However, in these numbers, they are repeating the first three #'s. The rest are duplicates I want to get rid of with the space, too. The column can be a hundred rows long. First, I need to know how I can "find" the ###_###. Second, "replace" with the first three ###'s. 

 

For example, I have a column with one-hundred rows of numbers. 90 are 700 C99. Five are 100 100, while another five are 200 200. I want the quickest way to find the latter 10 and get rid of the repeating number, so that it only says 100 and 200, respectively. How do I do that?

excel
Formulas and Functions
Macros and VBA

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 13, 2022

    statejj16 Alternatively, and in case you prefer not to use VBA a simple, but perhaps not very elegant, formula comparing the first three characters with the last three can create the numbers how you want them. Then copy/paste as values the end result on top of the original column. With the numbers starting from in A1 that could be:

    =IF(LEFT(A1,3)=RIGHT(A1,3),LEFT(A1,3),A1)

     and copy down.

    If your Excel version supports dynamic arrays and the numbers are in A1:A100, try this:

    =IF(LEFT(A1:A100,3)=RIGHT(A1:A100,3),LEFT(A1:A100,3),A1:A100)

     

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Feb 13, 2022

    statejj16 

     

    If you are okay with a vba suggestion, you could try this (change the range reference to whatever your actual range is). And, be sure to back up your data before trying to ensure it's what you're looking for.

     

    Sub test()
     Dim cell As Range
     
     With CreateObject("VbScript.RegExp")
          .Pattern = "(\d{3}) \1"
     
          For Each cell In Range("A1:A100")
               If .test(cell.Value) Then
                    cell.Value = .Replace(cell.Value, "$1")
               End If
          Next cell
     End With
     
End Sub

     

Resources