Forum Discussion

liz123395's avatar
liz123395
Copper Contributor
Sep 20, 2024
Solved

Only keeping a number/letter combination in a column

Hi all, 

 

I want to Find and replace a column that has a bunch of rows with a text that says 

"Liz A on 09/18/2024: 003TV00000DiIg6YAF"

 

The problem is the column will have a bunch of variety between the rows- for instance: 

"Liz A on 9/19/2024: 9349943GTH4i98494"

"Liz A on 10/21/2023: 49849q1i9H0390339"

and so on (different number/letter variation and different text around it (the quotation and formatting stays the same but the info is different))

It could even be another user so the user changes. 

 

I am wanting to delete all information in this column except the number/letter combination. But since the information around the number/letter combination can be different I'm not sure of an easy way to remove all of that text around it. Does anyone know of a way to go about this?

  • so if i understand correctly you want only the part from the ":" to the final "?
    so you could do that either using a formula in another column like:
    =TRIM(TEXTBEFORE(TEXTAFTER( A1, ":"),""""))
    or in the old Excel
    =MID( LEFT(A1, LEN(A1)-1), SEARCH(":",A1)+1,LEN(A1))
    or you can replace the text using the Find & Replace in excel:
    select the column
    hit ctrl+H to open the Find and Replace
    in the Find what you can type *: (note you want to have a space afterward to also remove that space)
    in the Replace with box leave it blank
    hit replace all (and IF it asks to continue with the rest of the document say no)
    repeat to remove the ending "
  • liz123395 

    Select the column.

    Press Ctrl+H to activate the Replace dialog.

    Enter *: followed by a space in the 'Find what' box.

    Leave the 'Replace with' box empty.

    Click 'Replace All'.

    Clear the 'Find what' box, then enter a double quote "

    Click 'Replace All' again.

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    so if i understand correctly you want only the part from the ":" to the final "?
    so you could do that either using a formula in another column like:
    =TRIM(TEXTBEFORE(TEXTAFTER( A1, ":"),""""))
    or in the old Excel
    =MID( LEFT(A1, LEN(A1)-1), SEARCH(":",A1)+1,LEN(A1))
    or you can replace the text using the Find & Replace in excel:
    select the column
    hit ctrl+H to open the Find and Replace
    in the Find what you can type *: (note you want to have a space afterward to also remove that space)
    in the Replace with box leave it blank
    hit replace all (and IF it asks to continue with the rest of the document say no)
    repeat to remove the ending "

Resources