Awkward concatenating problem

[mcornbill]

Hi all

Probably a simple answer to this one. I have 3 cells of data which I want to concatenate. However one of the cells is in a custom format which is padding out any values in it with leading zeroes to make it 12 characters in length. E.g:

567.78 becomes 000000567.78
3.20 becomes 000000003.20
etc etc

When this cell is concatenated it loses it's leading zero formatting. I've tried everything I can think of but short of actually typing the numbers manually using an ' at the beginning, I'm stumped!

Cheers
Mark

 

[Jonmo1]

couple ways to do it...

=REPT(0,12-LEN(A1))&A1
or
=Right("000000000000" &A1,12)

 

[mcornbill]

Cheers jonmo

Close, but doesn't quite do what I need it to. My value field are all 12 digits long now but if the original value happened to be 54.00 it turns it into 000000000054, rather than 000000054.00 If the value happened to be 54.20 it turns it into 00000000054.2, rather than 000000054.20. So excel is discarding any decimals it thinks it doesn't need and then putting extra zeroes at the front of the string to make it 12 characters. Normally this would be fine but this file is for a BACS run which takes fixed widths out of the string I'm concatenating, so the last 2 digits have to be pence even if it is 0 pence.

Hmmmmmmm........

 

[Gerald Higgins]

What does Jonmo1's second suggestion do in your example of 54.2 ?

 

[Norie]

Try this.

=TEXT(A1, "0000000000.00")

 

[Jonmo1]

=RIGHT("000000000000" &TEXT(A1,"00.00"),12)
=REPT(0,12-LEN(TEXT(A1,"00.00")))&TEXT(A1,"00.00")


edited...

I like Nories better. Way simpler. Wierd that I thought to use the Text function to account for decimals...but couldn't put it any further than that...LOL.

 

[mcornbill]

Bingo!

Thanks Norie, Gerald and Jonmo that's working perfectly now.

Cheers
Mark