Numeric input beginning with 0

G

Guest

Guest
Hello everybody,

When I have to input a numeric value beginning with 0 in a cell, I have to input using a quote i.e. '01234 As a result, XL remove this quote and show 01234

When I try to read a .txt file (tab delimited) with a '01234 value, XL show '01234

I think that XL store he ' as a special value, but which one ?

Thanks in advance for your help
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
If you put an apostophe ' in front of anything in a cell Excel interprets that as text. If you type '0123 you'll always see 0123 in the cell. If you type 0123 you might see either 123 or 0123 depending on the format of the cell. If you want to use leading zeros then format the cells as text (Format, Cells, choose Number tab and select Text).

HTH,
D
 
Upvote 0
Hi dk

If you format the cell as Text the content will also be Text (no difference than using ').

The ' is a hidden character that even the Find will not return (which is a pain!).

As I have said above to keep it numeric just use a custom format of:
"0000"
Depends on how many zeros you want of course.
 
Upvote 0
On 2002-03-12 04:09, Dave Hawley wrote:
Hi dk

If you format the cell as Text the content will also be Text (no difference than using ').

The ' is a hidden character that even the Find will not return (which is a pain!).

As I have said above to keep it numeric just use a custom format of:
"0000"
Depends on how many zeros you want of course.
I've no problem when I input a value with the keyboard; I just need to do the same with a .txt file. What is the Hex code for this hidden char '

Thanks
 
Upvote 0
I'm OK, Alt+0+39 is the ' character, but if I put this character in a .txt file (before a 0), XL show me '0 and not 0 as when I input thru the keyboard '0
 
Upvote 0
On 2002-03-12 05:03, Anonymous wrote:
I'm OK, Alt+0+39 is the ' character, but if I put this character in a .txt file (before a 0), XL show me '0 and not 0 as when I input thru the keyboard '0

The apostrophe is treated differently for entered data than it is for imported data. If you want the apostrophe recognized for imported data you can (as a 2nd step) select the imported data and use the Edit | Replace... menu command to replace an apostrophe (') with an apostrophe ('). Otherwise, don't include apostrophes in your tab delimited text file, use the Text to Columns... tool to import your data, and apply the Text format to the column of values with leading zeroes.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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
Back
Top