I am trying to concantenate something like this:
A B C
1 dog100 0001 red
2 0002
3 0003

I have fields A,C set as Text; field B set as custom "0000"
When I enter all the data, field B shows the beginning zeros: "0000, 0001,
etc." as it should.

If I construct a formula to increment column B by one: Sum(B1,B2) or
"B1+B2" and copy it down the column." The results are correct: 0001, 0002,
0003, etc.

The concantenation I want is thus:
"dog1000001red"
"dog1000002red"
"dog1000003red"

By using absolute references for $A$1, $C$1, and the normal referal
reference for B2 (sum), it does increment by one, but I loose the beginning
zeros:
"dog1001red, dog1002red, dog1003red."

I thought since column A has numbers in it and it's reading as text, maybe
it needs to be set to custom, 0000 instead, (which really doesn't make any
sense, but I did it anyway) and there was no change.

I don't know how to keep the beginning zeros when concantenating text (or
general) + a formated number: 0000.

Any ideas on how to keep the zeros when the concantenation takes place?

Thanks,
Bob

Hi Bob,

Try

=A$1&TEXT(B1,"0000")&C$1

--

HTH

RP
(remove nothere from the email address if mailing direct)


"sunslight" wrote in message
...
I am trying to concantenate something like this:
A B C
1 dog100 0001 red
2 0002
3 0003

I have fields A,C set as Text; field B set as custom "0000"
When I enter all the data, field B shows the beginning zeros: "0000, 0001,
etc." as it should.

If I construct a formula to increment column B by one: Sum(B1,B2) or
"B1+B2" and copy it down the column." The results are correct: 0001,
0002,
0003, etc.

The concantenation I want is thus:
"dog1000001red"
"dog1000002red"
"dog1000003red"

By using absolute references for $A$1, $C$1, and the normal referal
reference for B2 (sum), it does increment by one, but I loose the
beginning
zeros:
"dog1001red, dog1002red, dog1003red."

I thought since column A has numbers in it and it's reading as text, maybe
it needs to be set to custom, 0000 instead, (which really doesn't make any
sense, but I did it anyway) and there was no change.

I don't know how to keep the beginning zeros when concantenating text (or
general) + a formated number: 0000.

Any ideas on how to keep the zeros when the concantenation takes place?

Thanks,
Bob

Bob,

I answered you, but it looks like the post was removed. --don't know why.

Your solution was exactly correct.

The only problem, I didn't quite ask for what I really wanted.

I guess I'll do a general post again and try to ask the right question.

Thanks,
Bob

"Bob Phillips" wrote:

Hi Bob,

Try

=A$1&TEXT(B1,"0000")&C$1

--

HTH

RP
(remove nothere from the email address if mailing direct)


"sunslight" wrote in message
...
I am trying to concantenate something like this:
A B C
1 dog100 0001 red
2 0002
3 0003

I have fields A,C set as Text; field B set as custom "0000"
When I enter all the data, field B shows the beginning zeros: "0000, 0001,
etc." as it should.

If I construct a formula to increment column B by one: Sum(B1,B2) or
"B1+B2" and copy it down the column." The results are correct: 0001,
0002,
0003, etc.

The concantenation I want is thus:
"dog1000001red"
"dog1000002red"
"dog1000003red"

By using absolute references for $A$1, $C$1, and the normal referal
reference for B2 (sum), it does increment by one, but I loose the
beginning
zeros:
"dog1001red, dog1002red, dog1003red."

I thought since column A has numbers in it and it's reading as text, maybe
it needs to be set to custom, 0000 instead, (which really doesn't make any
sense, but I did it anyway) and there was no change.

I don't know how to keep the beginning zeros when concantenating text (or
general) + a formated number: 0000.

Any ideas on how to keep the zeros when the concantenation takes place?

Thanks,
Bob