Custom cell format for cells containing 2 values

[Bigera]

Hi,
I'd like to make a custom cell format for cells containing 2 values separated by a dash.

Values entered like this:
13224 should be displayed as 13-00245
151244 shoud be displayed as 15-01244 - and so on.

I have no problem to add a dash or leading zeros.
But how can I combine these tasks, making sure, that the first two digits show up on the left, followed by the dash, followed by a five-digit number with leading zeros?

Any ideas?
Thanks in advance
Stefan

 

[trprasad78]

its a 13-00245 or 13-00224 ?

you want like this
13224 should be displayed as 13-00245

or
13224 should be displayed as 13-00224

 

[Bigera]

sorry, that's a typo:
13224 should be displayed as 13-00224
151244 shoud be displayed as 15-01244
1433224 shoud be displayed as 14-33224 (no leading zeros here), and so on.

 

[trprasad78]

Please check the attached file, this may help you.

=CONCATENATE(LEFT(A2,2),REPT(0,IF(LEN(MID(A2,3,100))<5,5-LEN(MID(A2,3,100)),0)),MID(A2,3,100))

 

[trprasad78]

you need hyphen right please use below formula.

=CONCATENATE(LEFT(A2,2),"-",REPT(0,IF(LEN(MID(A2,3,100))<5,5-LEN(MID(A2,3,100)),0)),MID(A2,3,100))

 

[Chihiro]

Alternate version.
=LEFT(A2,2)&"-"&TEXT(RIGHT(A2,LEN(A2)-2),"00000")

 

[bosco_yip]

1] Here is my proposal, the result appeared some difference with trprasad78/Chihiro's formula.

=LEFT(A2,MAX(LEN(A2)-5,2))&"-"&RIGHT("00000"&RIGHT(A2,MAX(LEN(A2)-2,0)),5)

2] Since the OP does not state, how about number is in 8 digits or over?

That will be the OP's choice: to always keep 4 digits at right, or always keep 2 digits at left ?

3] Enclosed the testing file in data from 1 digit to 9 digits for comparison.

Regards
Bosco

 

[Deepak]

If by formula acceptable then this might too..

=REPLACE(REPLACE(A2,3,0,REPT(0,7-LEN(A2))),3,0,"-")

 

[Bigera]

Hi guys, thanks for your posts.

I already found the formula posted by Chihiro

=LEFT(A2,2)&"-"&TEXT(RIGHT(A2,LEN(A2)-2),"00000")

which transforms a number into the desired format contained in another cell. Unfortunately, I need this to be done using custom cell formatting. That is, I'm trying to translate the formula into a custom cell format that can be used in Format Cells > Custom > Type ... dialog.

Sorry for not being precise enough right from the start ...

 

[NARAYANK991]

Hi ,

I am not sure that it can be done using a Custom cell format.

However , you can use Conditional Formatting , as shown in the uploaded workbook.

Narayan

 

[Chihiro]

AFAIK - custom number format unfortunately is not possible. CF, VBA, or Helper column would be the solution.

 

[Bigera]

Thanks again for contributing.
Narayan, you CF solution works fine :D
However, the Excel version I'm using here (Excel Mac 2011) doesn't show the rule content. Instead, I have tabs for Font, Border and Patterns only - no number formatting here :/ I'll have to check later with my Windows machine when I'm back in office.
In the meantime, would you mind sending a screenshot showing the number format rule you defined?

 

[NARAYANK991]

Hi ,

There are 3 rules which have been used ; if your requirement is for a number with less than 5 digits or more than 7 digits , you will need to use more rules.

From top to bottom , the rules and their formats are :

=$A1 > 999999 .................................................. 00-00000

=AND($A1 > 99999, $A1 < 999999) .................... 00-\00000

=$A1 < 99999 .................................................... 00-\0\0000

Narayan

 

[bosco_yip]

13224 should be displayed as 13-00224
151244 shoud be displayed as 15-01244
1433224 shoud be displayed as 14-33224 (no leading zeros here), and so on.

Try,

Select range B1:B3 >> Custom Cell Format >> in the Type box enter :

[>999999]00-00000;[<99999]00-\0\0000;00-\00000

Regards
Bosco

 

[Bigera]

Thanks, Bosco!
This seems to work, too. Except for values < 10000.
e.g. 9999 results in 09-00999 instead of 99-00099.
Do you think your solution can be adjusted in this respect?
It's very unlikely that I'll have to deal with values like this, that's why I didn't use it as an example initially.

Stefan