Cell Format

[MarkAn]

Hello

I am trying to format a cell so that it accepts UK Bank Sort Code data.

I want the cell to be formatted 00-00-00, however, I used cell format but some combinations of numbers entered, convert it to date format.

Can anyone please suggest, how I can format the cell, so that if either a 6 digit number is typed, it will display as 12-34-56, or if the user types including '-', is shows as such.

Many thanks in advance

 

[John Davis]

Have you tried setting up a custom format? It seems too work.

 

[MarkAn]

Could you let me know, what custom format you used please, as the one that I used, works fine if typing the numbers without '-', however, as above, if I type the numbers with '-' it reverts it to a date, I.e. if I type 40-18-36, it remains as this, however, if I type 10-12-12, it reverts it to 04-21-53 (the numerical date format).

Whereas if I typed 101212, it formats it correctly

Any help is much appreciated.

 

[vlady]

try it like this
_(* ##"-"##"-"##_)

or
##"-"##"-"##

 

[MarkAn]

Thank you for your suggestion, however, that also doesn't work.

Excel is looking at the figures entered, as if the first 2 are between 01 and 31 and the second 2 are between 01 and 12, it thinks that it is a date (by entering in "-") and coverts it.

I will have to think of another way.

Many thanks

 

[vds1]

Try format cell > Custom > Type,

00-00-00

 

[Matt Rogers]

Hi.

Excel Workbook
	A	B
1	1	00-00-01
2	12	00-00-12
3	123	00-01-23
4	1234	00-12-34
5	12345	01-23-45
6	123456	12-34-56
7		00"-"00"-"00
Sheet