Display Hidden Characters

ds_robb

Board Regular
Joined
Aug 25, 2002
Messages
72
I have an Excel spreadsheet that has been created from multiple .CSV files. In some cells there will be a number 605100011781 and in others 0605100011781 (Preceding 0 placed in bold to highlight the difference). They are or should be exactly the same, but Excel sees one as a number and the other as general/text. Normally if you place a preceding O in front you would hit ' in Excel and when you look at the cell, this would be shown '0605100011781 Here it isn't, so there is some other hidden character keeping the format different from what I want. If on the formula line I place my cursor in front of the 0 and just backspace, then Excel will treat it as a number again and show it as 605100011781 I need to see what that special character is so I can replace is as there are thousands of lines and thousands of different numbers all with the same issue. Is there a way to show what that hidden character is in front of the number?

Many thanks
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
Try this and see if it straightens things out. Select the entire column and then bring up the Text To Columns dialog box (Data tab, Data Tools panel)... as soon as it appears, click the Finish button. Did that straighten things out?
 

ds_robb

Board Regular
Joined
Aug 25, 2002
Messages
72
Try this and see if it straightens things out. Select the entire column and then bring up the Text To Columns dialog box (Data tab, Data Tools panel)... as soon as it appears, click the Finish button. Did that straighten things out?
Thanks Rick. Partially. I performed the Text to Column on the "raw data" but when I ran a pivot, I had to perform same on the pivot data for some reason. I still ideally would like to see what the character is so I could do a global replace of it. There clearly is something preceding the value so it retains it's leading zero, but it's not a ' and whatever it is, it doesn't show on the formula bar. I even tried copying the cell value into wordpad/notepad. but no joy.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
What do you get if you point this at one of the cells in questions
=Code(A1)
 

ds_robb

Board Regular
Joined
Aug 25, 2002
Messages
72

ADVERTISEMENT

What do you get if you point this at one of the cells in questions
=Code(A1)
Thanks, presuming A1 is a cell reference and I adjust for the two specific cells.... for the 605100011781 I get 54 and for the same but with preceding zero and hidden special character I get 48
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
48 is the ASCII code for the number 0 so you do not have a hidden character in front of the 0.

What is the cell format for the cells that do not work correctly with my procedure? If they are text, change the column to General and then apply the procedure I posted in Message #2.
 

ds_robb

Board Regular
Joined
Aug 25, 2002
Messages
72
48 is the ASCII code for the number 0 so you do not have a hidden character in front of the 0.

What is the cell format for the cells that do not work correctly with my procedure? If they are text, change the column to General and then apply the procedure I posted in Message #2.
The cells were in general format anyway and an image below. The only difference between the two is one set has a preceding zero as explained above. However what I don't understand is if I am in the cell that contains one of the numbers that is preceded with a zero (left aligned on the image below), on the formula bar I position my cursor directly in front of that zero and just hit the backspace key and then click the mouse into another cell (i.e, I don't hit newline or anything else), the cell will change the value display to 6.051E+11 (i.e. numeric) rather than 0605100011781. I didn't delete the zero or change the field type, so why is Excel now representing the value differently when as I said, all I did was backspace from being in front of the leading zero?
 

Attachments

  • Numbers.png
    Numbers.png
    4.1 KB · Views: 2

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
Any chance you can post the workbook to DropBox so that we can see the exact data that you see?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,859
Messages
5,627,290
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top