Formatting in Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dragon - 19 Oct 2004 15:03 GMT

Hi, I am trying to concatenate a few columns but having trouble with number
format

Column A: ABCDEF
Column B: 015 (this col is formatted with 'Number 000')
Column C: XYZ

Formula A1&B1&C1

I am getting ABCDEF15XYZ
I need ABCDEF015XYZ

Any ideas how would I go about formatting the number in the formula so it
shows leading zero?

Thank you.

Reply to this Message

scottymelloty - 19 Oct 2004 15:23 GMT

The only way i can think is to format the cell with 015 AS TEXT the
retype it in, this will work for your cocantenate but will stop yo
adding up the number if u need to do tha

Reply to this Message

scottymelloty - 19 Oct 2004 15:33 GMT

actually something that is better

=concatenate(a1,0,b1,c1)

just put a zero in between two commas before the number cell

that way you can add the numbers as wel

Reply to this Message

Dragon - 19 Oct 2004 16:21 GMT

Thank you Scott.

Col B was incrementing and adding 0 manually wouldn't work after it reaches
100 and made it 4 digits.

Thank you though.

> actually something that is better
>
[quoted text clipped - 3 lines]
>
> that way you can add the numbers as well

Reply to this Message

Don Guillett - 19 Oct 2004 15:36 GMT

try
=a1 & text(b1,"000") & c1

Signature

Don Guillett
SalesAid Software
donaldb@281.com

> Hi, I am trying to concatenate a few columns but having trouble with number
> format
[quoted text clipped - 12 lines]
>
> Thank you.

Reply to this Message

Dragon - 19 Oct 2004 16:19 GMT

Thank you Don. Worked awesome.

> try
> =a1 & text(b1,"000") & c1
[quoted text clipped - 16 lines]
>>
>> Thank you.

Reply to this Message

Don Guillett - 19 Oct 2004 16:25 GMT

glad to help

Signature

Don Guillett
SalesAid Software
donaldb@281.com

> Thank you Don. Worked awesome.
>
[quoted text clipped - 18 lines]
> >>
> >> Thank you.

Reply to this Message