SOLVED

Search/Replace With Wildcard without changing the wildcard characters

%3CLINGO-SUB%20id%3D%22lingo-sub-919661%22%20slang%3D%22en-US%22%3ESearch%2FReplace%20With%20Wildcard%20without%20changing%20the%20wildcard%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919661%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Brains%20Trust%20-%20I%20hope%20you%20can%20assist.%20I%20haven't%20been%20able%20to%20find%20the%20answer%20elsewhere.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20do%20a%20search%2Freplace%2C%20using%20wildcards%20to%20locate%20the%20target%2C%20but%20in%20the%20replace%20part%20of%20the%20action%2C%20I%20want%20to%20leave%20the%20wildcard%20characters%20as%20they%20were.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20find%20all%20cases%20of%20%23%3F%3F%3F%20(followed%20by%20a%20space)%20-%20then%20change%20the%20spaces%20to%20a%20pipe.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20I%20could%20use%20replace%20with%3A%20%23%3F%3F%3F%7C%3C%2FP%3E%3CP%3EBut%20that%20actually%20inserts%20question%20marks.%20Is%20there%20a%20way%3F%20It%20seems%20like%20it%20would%20be%20a%20very%20common%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EKevin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-919661%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-919702%22%20slang%3D%22en-US%22%3ERe%3A%20Search%2FReplace%20With%20Wildcard%20without%20changing%20the%20wildcard%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919702%22%20slang%3D%22en-US%22%3EYou%20can't%20use%20wildcards%20in%20the%20Replace%20box%2C%20but%20you%20could%20use%20a%20formula%20in%20another%20column%2C%20to%20add%20the%20pipe.%20Then%2C%20copy%20the%20formula%20results%2C%20and%20paste%20as%20values%20over%20the%20original%20data.%20(Make%20a%20backup%20of%20your%20file%20first)%3CBR%20%2F%3EFor%20example%2C%20if%20there's%20only%20one%20%23%20in%20the%20original%20data%2C%20use%20this%20formula%2C%20where%20the%20data%20is%20in%20cell%20B3%3A%3CBR%20%2F%3E%3DREPLACE(B3%2CFIND(%22%23%22%2CB3)%2B4%2C0%2C%22%7C%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-920226%22%20slang%3D%22en-US%22%3ERe%3A%20Search%2FReplace%20With%20Wildcard%20without%20changing%20the%20wildcard%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-920226%22%20slang%3D%22en-US%22%3EThank%20you%20-%20I%20think%20I%20can%20make%20that%20work%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello Brains Trust - I hope you can assist. I haven't been able to find the answer elsewhere.

 

I'm trying to do a search/replace, using wildcards to locate the target, but in the replace part of the action, I want to leave the wildcard characters as they were.

 

For example, find all cases of #??? (followed by a space) - then change the spaces to a pipe.

 

I thought I could use replace with: #???|

But that actually inserts question marks. Is there a way? It seems like it would be a very common need.

 

Thanks,

Kevin

2 Replies
Highlighted
Best Response confirmed by kevmartin (New Contributor)
Solution
You can't use wildcards in the Replace box, but you could use a formula in another column, to add the pipe. Then, copy the formula results, and paste as values over the original data. (Make a backup of your file first)
For example, if there's only one # in the original data, use this formula, where the data is in cell B3:
=REPLACE(B3,FIND("#",B3)+4,0,"|")
Highlighted
Thank you - I think I can make that work