Input Mask

[Diesel9a1]

Normally this would be Format Cells / Number / Custom, then enter my mask, but I'm struggling with this one.
I need the following:
086/63192

displaying as:
0086/063192
(Four/Six) format.

Excel needs to add zeros at the start of the first part and the second part of the number to pad it out correctly.

Any thoughts?

Thanks in advance.

Pete

 

[jmacleary]

If your data is ALWAYS in the form of 3 digits then slash then 5 digits, this will do it: =TEXT(LEFT(A1,3),"0000")&"/"&TEXT(RIGHT(A1,5),"000000"), but obviously in a separate cell.

 

[Diesel9a1]

Putting your formula in cell B1 Jim, I get the following:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]56/3564[/TD]
[TD]56///3564[/TD]
[/TR]
</tbody>[/TABLE]

In B1, I'm looking to see 0056/003564

 

[jmacleary]

Yeah thats why I asked if the format of the data was always as you wrote in the example - it obviously isn't - however, there is a way forward just give me a few minutes to work on it.

 

[jmacleary]

Try this: =TEXT(LEFT(A1,FIND("/",A1)-1),"0000")&"/"&TEXT(MID(A1,FIND("/",A1)+1,6),"000000")

 

[Diesel9a1]

Try this: =TEXT(LEFT(A1,FIND("/",A1)-1),"0000")&"/"&TEXT(MID(A1,FIND("/",A1)+1,6),"000000")

Now that is very impressive. Thanks ever so much for your time Jim.

Pete