Concatenating columns with formatting

[Ken]

HI

I have a column of numbers

123 and then another column that has 3 digits.
BUT That column we format using 000000 so that it displays
like 000234 so our spreadsheet is like this

123 000234

I would like to concatenate the two.
When we do it does 123234
and we want 123000234
Can you help me?
Thanks in advance.
Ken

 

[CLR]

Hi Ken...........

If you can tolerate the second column being formatted as TEXT, it will
concatenate as you want........

Vaya con Dios,
Chuck, CABGx3

 

[Ken]

I'm sorry I was not clearer.
We already use a custom formatting 000000
that adds 3 zeros to the number. So
I don't understand how your answer could work.
Sincerely,
Ken

 

[CLR]

Hi Ken............

Another way........(all on one line)

=IF(LEN(B1)=1,A1&"00000"&B1,IF(LEN(B1)=2,A1&"0000"&B1,IF(LEN(B1)=3,A1&"000"&
B1,IF(LEN(B1)=4,A1&"00"&B1,IF(LEN(B1)=5,A1&"0"&B1,IF(LEN(B1)=6,A1&B1,"")))))
)

Vaya con Dios,
Chuck, CABGx3

 

[Juan Sanchez]

Ken

CLR's answer would work if instead of the custom format
000000 you formated the column as text and when inputing
the number type all leading zeroes... you can avoid that
by using:


=A1&TEXT(B1,"000000")

No need to format as text the other column but this result
*will* be text... if you want numeric properties on your
concatenated result the use:

=--(A1&TEXT(B1,"000000"))

cheers
Juan

 

[CLR]

How cool that is Juan...........another one for my goodie-box stash,

Thanks
Vaya con Dios,
Chuck, CABGx3

 

[AWESOME THANKS SO MUCH]

-----Original Message-----
Ken

CLR's answer would work if instead of the custom format
000000 you formated the column as text and when inputing
the number type all leading zeroes... you can avoid that
by using:


=A1&TEXT(B1,"000000")

No need to format as text the other column but this result
*will* be text... if you want numeric properties on your
concatenated result the use:

=--(A1&TEXT(B1,"000000"))

cheers
Juan

.