Tiny ring cannot be removed

[Curious4more]

Hi Guys,
This could be an easy one for you more experienced but for me is becoming a nightmare right now. I've just pulled a report from SAP and one of the columns which supposed to store numbers have a tiny ring in each cell somewhere above between the last two digits. I did manage to convert it to value but still having the tiny ring above and functions like VLOOKUP of course do not work.
Anyone could help me out with a quick fix?

 

[Rick Rothstein]

Thanks for that. So now I have unicode no. 3328. Sorry to be a pain but how can I replace it? because View attachment 185573328 do not work for me in the find field.

Not sure it this will work or not, but try this. Select a cell with that symbol in it. Go into the Formula Bar and select that single character, Press CTRL+C to copy it into the Windows Clipboard. Press Esc to get out of edit mode. Select the column, call up the Replace dialog box and put that symbol into the Find What field by pressing CTRL+V. Proceed with the rest of the dialog as you already know how to.

 

[sandy666]

Thanks sandy666 I'm afraid I cannot share it as it is a sort of sensitive company stuff. And as you've probably noticed I'm kind of a newbie but later I'll definitely take a look to XL2BB because I'm curious.

you can share workbook with this column only or XL2BB
will be much faster and easier than guessing what will be if will be

 

[Curious4more]

I think I've done it with this function: =VALUE(LEFT(A2;LEN(A2)-1))
Thank you ALL for your valuable posts. I've already learned a lot from you guys. Much appreciated.

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Curious4more]

Not sure it this will work or not, but try this. Select a cell with that symbol in it. Go into the Formula Bar and select that single character, Press CTRL+C to copy it into the Windows Clipboard. Press Esc to get out of edit mode. Select the column, call up the Replace dialog box and put that symbol into the Find What field by pressing CTRL+V. Proceed with the rest of the dialog as you already know how to.

Thank you Rick, I'm done now, but no I wasn't able to take that one character only and copy.

 

[JBRob72227]

I've had similar problems with garbage output when the SAP output processor mis-translates special characters in the SAP source data. This usually resulted when someone copies and pastes online data containing things like a copyright symbol into a SAP field. In your case, it looks like Excel interprets the output as a degree symbol or ASCII U_00B0. Check your fields in the original SAP data for special characters. It's been several years, but as I recall the solution was to translate everything to UNICODE, search for the offending string and replace all with nulls.