Forum Discussion

Testbot1's avatar
Testbot1
Copper Contributor
Aug 08, 2023
Solved

Removing text from excel

I want to remove large amount of texts from the A column.  For example, I want the first  values to read Value Pack Sticks and  the second value to read Crunchy Clams. How can I achieve this in mass ...
excel
  • mtarler's avatar
    mtarler
    Aug 08, 2023

    IF the pattern is always: 4 space 2 space 4 space WHATyouWANT .pdf
    then you could use:
    =MID(A1:A1000, 14, LEN(A1:A1000)-17)

    Adjust A1:A1000 to the range you need and if you don't want to keep the original you can COPY the result and PASTE VALUES and then delete the original.

    Another Alternative is to use the Text To Columns function on the DATA tab. I think it will take 2 "passes".  On 1 pass it is fixed column width where you remove the first part of the column:

    note how I mark the first column to skip it and not use it.

    Then on 2nd use you can delineate the columns using the "." character to strip off the .pdf portion.

     

     

     

mtarler's avatar
mtarler
Silver Contributor
Aug 08, 2023

IF the pattern is always: 4 space 2 space 4 space WHATyouWANT .pdf
then you could use:
=MID(A1:A1000, 14, LEN(A1:A1000)-17)

Adjust A1:A1000 to the range you need and if you don't want to keep the original you can COPY the result and PASTE VALUES and then delete the original.

Another Alternative is to use the Text To Columns function on the DATA tab. I think it will take 2 "passes".  On 1 pass it is fixed column width where you remove the first part of the column:

note how I mark the first column to skip it and not use it.

Then on 2nd use you can delineate the columns using the "." character to strip off the .pdf portion.

 

 

 

  • Testbot1's avatar
    Testbot1
    Copper Contributor
    Aug 08, 2023
    This was very helpful. Thank you!

Resources