Forum Discussion
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 ...
- 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 "
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.