Code Done Right!

Remove duplicated text

This procedure aims to remove multiple instances of text put in single cell. Sometimes if you copy from a pdf file that has not been properly prepared it happens that when you paste, it pastes multiple times the same content. Check how many times it is duplicated and then run the below procedure. It will ask you how many times text has been duplicated.

Sub DupeTextRm()
    Dim CellValue As String
    Dim CellLen As Integer
    Dim CellOutput As String
    Dim TextDupeVar As Integer
    
    Selection.NumberFormat = "@"            'Changes the cell type to text
    TextDupeVar = InputBox("How many times is the text duplicated?", "DupeTextRm") 'Asks how many times text is duplicated
    CellValue = ActiveCell.Value            'Loads active cell's contents to CellValue
    CellLen = Len(CellValue)                'Counts number of characters in the active cell
    CellLen = CellLen / TextDupeVar         'Divides number of characters in the active cell by repetition time
    CellOutput = Mid(CellValue, 1, CellLen)   'Loads 1/CellLen of active cell's contents to CellOutput
    
    ActiveCell.Value = CellOutput   'Spews out the result
End Sub

Above video depicts the use of the procedure, when video looks frozes it actually displays an input box waiting for user input. Put the number of times the text is duplicated. Windows xbox screengrab only takes the active window, sorry, for now I don’t have anything better.