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

Copper Contributor

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?

2 Replies

@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

 

@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)