Numeric input beginning with 0

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

[Dave Hawley]

Hi

Have you tried using a custom format like:

"0000"

[dk]

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

[Dave Hawley]

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.

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

[Dave Hawley]

Alt+0+39

39 Is the Character number.

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

[Mark W.]

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.