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