number formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Louise - 22 Nov 2006 13:52 GMT

Hi all

I need to enter numbers into a column with the following format 1-21 (a
number, then a dash, then another number).

When I do this, Excel changes the numbers into dates. I have tried creating
a custom number but this still doesn't work. The only method I have found
which works is to create a custom number format for every single cell,
entering the exact number in the 'type' box.

Is there a quicker way???

Thank you
Louise

Reply to this Message

Dave Peterson - 22 Nov 2006 14:08 GMT

Maybe just select the range to fill
format|cells|Number tab|Custom category
0-00

And then just enter your 3 digits (no dashes).

If you do this, the values will still be numeric (121), but will show 1-21.

If you want the values to include the dash, you can format the cells as Text and
then type exactly what you want or just precede your entry with an apostrophe:
'1-21

> Hi all
>
[quoted text clipped - 10 lines]
> Thank you
> Louise

Signature

Dave Peterson

Reply to this Message

Louise - 22 Nov 2006 14:37 GMT

That worked a treat, thank you!!

(Just out of curiosity, if some of the figures were 5 digits rather than 3,
is there a way around this), ie. some are 1-22 but some could be 12-434.

Thanks again.
Louise

> Maybe just select the range to fill
> format|cells|Number tab|Custom category
[quoted text clipped - 22 lines]
> > Thank you
> > Louise

Reply to this Message

Dave Peterson - 22 Nov 2006 15:40 GMT

You can still use the text stuff or you could use a custom format of:

[<1000]0-00;00-000

> That worked a treat, thank you!!
>
[quoted text clipped - 34 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Epinn - 23 Nov 2006 20:03 GMT

Dave,

I have a question from this thread. If you have time would you mind helping me? It is in the Newusers forum. Subject: custom format. 11/22/2006

Appreciate clarification. Thanks.

Epinn

You can still use the text stuff or you could use a custom format of:

[<1000]0-00;00-000

Louise wrote:

> That worked a treat, thank you!!
>
[quoted text clipped - 38 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Prasadkakarla - 24 Nov 2006 19:16 GMT

Mr.Louise
i am new person for this forum. anyway i think my answer is useful for u
your number for ex. 1-21 is the result but u use this number for 2
columsn like

A B RESULT
1 21 = CONCATENATE (A1,"-",B1)

21 141 = " ( SAME FORMULA USE) U will get 21-141
very easy mathod after finish you work result colum just copy and paste
special there only in values you will get result easy , there is no
restriction for digits.

colulmn A and column B filling is depend upon ur s request number "-"
symbol automatic generated n formulae. ( prasadkakarla007@gmail.com)
check and send a mail how it is working ok

> Hi all
>
[quoted text clipped - 10 lines]
> Thank you
> Louise

Reply to this Message