Delete "?" from text

[SimonHughes]

Hello, I download reports into Excel where an apostrophe (') is returned as a question mark (?) which results in the word men's showing as men?s
I would like to replace but using the Replace function just deletes all of the text. Can anyone give me a solution to this?

 

[RobP]

Book1.xlsb
	H	I	J
2
3		men?s	men's
4
Sheet5

Cell Formulas
Range	Formula
J3	J3	=SUBSTITUTE(I3,"?","'")

 

[RobP]

or this if you have a lot of rows to save copying down:

=BYROW(I3:I8,LAMBDA(row,SUBSTITUTE(row,"?","'")))

 

[Akuini]

Try using Replace dialog box.
In Find What field type ~?
In Replace What field type '

 

[Sanjeev1976]

You can use tilde sign in front of the ? in the Find what box like ~? and then the find and replace will work

 

[SimonHughes]

Book1.xlsb
	H	I	J
2
3		men?s	men's
4
Sheet5

Cell Formulas
Range	Formula
J3	J3	=SUBSTITUTE(I3,"?","'")

Yes, this works perfectly thanks. I see a different solution below which is what I will use in this instance, but thank you for replying, appreciated!

 

[SimonHughes]

You can use tilde sign in front of the ? in the Find what box like ~? and then the find and replace will work

Yep, this is the solution for this problem, many thanks

 

[SimonHughes]

Try using Replace dialog box.
In Find What field type ~?
In Replace What field type '

Yep, this is the solution for this problem, many thanks

 

[RobP]

Yes, this works perfectly thanks. I see a different solution below which is what I will use in this instance, but thank you for replying, appreciated!

youre welcome, thanks for feedback.

Rob