Concatenate text and numbers?

I need a formula to concatenate A1,B#,C1; and place the result in D2, D3, D...

A B C D
1 dog100 0000 red ""
2 0001 dog1000001red
3 0002 dog1000002red

A1: Does not change its location. It is an absolute reference.
It is in general format, a combination of text and a number. It is imported
that way.

B2: is a custom format number of the form "0000". I have to keep all the
digits. It is derived from a formula to increment the # in the previous
column, by one.

C3: Does not change its location. It is an absolute reference.
It is Text.

What I want is to end up with D2, D3,.. a combination of text and value,
derived by combining A1,B...,C1.
Then copy the formula down the D column, so it will referentialy calculate
the next answer.

Can someone help me put together text + numbers that increment?

Thanks,
Bob

in D2:
=$a$1&text(b2,"0000")&$c$1
dragged down

Or maybe:
in D2:
=$a$1&text(row()-1,"0000")&$c$1





sunslight wrote:
>
> I need a formula to concatenate A1,B#,C1; and place the result in D2, D3, D...
>
> A B C D
> 1 dog100 0000 red ""
> 2 0001 dog1000001red
> 3 0002 dog1000002red
>
> A1: Does not change its location. It is an absolute reference.
> It is in general format, a combination of text and a number. It is imported
> that way.
>
> B2: is a custom format number of the form "0000". I have to keep all the
> digits. It is derived from a formula to increment the # in the previous
> column, by one.
>
> C3: Does not change its location. It is an absolute reference.
> It is Text.
>
> What I want is to end up with D2, D3,.. a combination of text and value,
> derived by combining A1,B...,C1.
> Then copy the formula down the D column, so it will referentialy calculate
> the next answer.
>
> Can someone help me put together text + numbers that increment?
>
> Thanks,
> Bob

--

Dave Peterson

If you want to use all 3 columns, then it seems like this would work for
you:
D1: =$A$1&TEXT(B1,"0000")&$C$1
Then copy down column D.

But...
If you really only need the values in Cells A1 and C1 and just want to
increment the middle section, would this work?
D1: =$A$1&TEXT(ROWS($1:1)-1,"0000")&$C$1
(again, just copy that formula down column D)

Is either of thosse what you're looking for?

Ron

Thank you both.

I will try and see if these will work for me.

What I forgot to state, is that the concatenation must not turn the numbers
into text. The numbers must stay numbers. (I tried a TEXT(x,"00")) type
formula. It put everything together, but I had all text instead of text,
numbers, text.

Bob

"Dave Peterson" wrote:

> in D2:
> =$a$1&text(b2,"0000")&$c$1
> dragged down
>
> Or maybe:
> in D2:
> =$a$1&text(row()-1,"0000")&$c$1
>
>
>
>
>
> sunslight wrote:
> >
> > I need a formula to concatenate A1,B#,C1; and place the result in D2, D3, D...
> >
> > A B C D
> > 1 dog100 0000 red ""
> > 2 0001 dog1000001red
> > 3 0002 dog1000002red
> >
> > A1: Does not change its location. It is an absolute reference.
> > It is in general format, a combination of text and a number. It is imported
> > that way.
> >
> > B2: is a custom format number of the form "0000". I have to keep all the
> > digits. It is derived from a formula to increment the # in the previous
> > column, by one.
> >
> > C3: Does not change its location. It is an absolute reference.
> > It is Text.
> >
> > What I want is to end up with D2, D3,.. a combination of text and value,
> > derived by combining A1,B...,C1.
> > Then copy the formula down the D column, so it will referentialy calculate
> > the next answer.
> >
> > Can someone help me put together text + numbers that increment?
> >
> > Thanks,
> > Bob
>
> --
>
> Dave Peterson
>

The result of a CONCATENATION is a TEXT string........

From Help.....
CONCATENATE
Joins several text strings into one text string.
Syntax:
CONCATENATE (text1,text2,...)
Text1, text2, ... are 1 to 30 text items to be joined into a single text
item. The text items can be text strings, numbers, or single-cell
references.

Of course, concatenated numbers can be stripped back out of a string and
returned to number status.......

Vaya con Dios,
Chuck, CABGx3


"sunslight" <[email protected]> wrote in message
news:[email protected]...
> Thank you both.
>
> I will try and see if these will work for me.
>
> What I forgot to state, is that the concatenation must not turn the
numbers
> into text. The numbers must stay numbers. (I tried a TEXT(x,"00")) type
> formula. It put everything together, but I had all text instead of text,
> numbers, text.
>
> Bob
>
> "Dave Peterson" wrote:
>
> > in D2:
> > =$a$1&text(b2,"0000")&$c$1
> > dragged down
> >
> > Or maybe:
> > in D2:
> > =$a$1&text(row()-1,"0000")&$c$1
> >
> >
> >
> >
> >
> > sunslight wrote:
> > >
> > > I need a formula to concatenate A1,B#,C1; and place the result in D2,
D3, D...
> > >
> > > A B C D
> > > 1 dog100 0000 red ""
> > > 2 0001 dog1000001red
> > > 3 0002 dog1000002red
> > >
> > > A1: Does not change its location. It is an absolute reference.
> > > It is in general format, a combination of text and a number. It is
imported
> > > that way.
> > >
> > > B2: is a custom format number of the form "0000". I have to keep all
the
> > > digits. It is derived from a formula to increment the # in the
previous
> > > column, by one.
> > >
> > > C3: Does not change its location. It is an absolute reference.
> > > It is Text.
> > >
> > > What I want is to end up with D2, D3,.. a combination of text and
value,
> > > derived by combining A1,B...,C1.
> > > Then copy the formula down the D column, so it will referentialy
calculate
> > > the next answer.
> > >
> > > Can someone help me put together text + numbers that increment?
> > >
> > > Thanks,
> > > Bob
> >
> > --
> >
> > Dave Peterson
> >

Thank you.

Hopefully I can stay on line long enough this time so I can reply to your
answers--my system died for several days, right after my last post.

Ok, that's as I thought: Concatentaion takes text & numbers and makes a TEXT
string.

You say I can strip out the numbers (text) and return them as numbers--I
don't know how to do that.

What I need is an alph numeric string, as:

sky1blue2rain
sky1blue3rain
sky1blue4sun
sky1blue5night

After the initial entry, whichever digit I want, in this case, the second,
is incremented and is a real number, not text.

Is there a forumla for that?

Or do I do the math, concatenate, then restore the (text)numbers to real
numbers, which I can do, but i don't know how to put them back into the
string.

Hopefully, there will be an easy way and a formula that will let me mix
modes, have an alphanumeric string, where the numbers can be manipulated.

Thanks,
Bob


"CLR" wrote:

> The result of a CONCATENATION is a TEXT string........
>
> From Help.....
> CONCATENATE
> Joins several text strings into one text string.
> Syntax:
> CONCATENATE (text1,text2,...)
> Text1, text2, ... are 1 to 30 text items to be joined into a single text
> item. The text items can be text strings, numbers, or single-cell
> references.
>
> Of course, concatenated numbers can be stripped back out of a string and
> returned to number status.......
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "sunslight" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you both.
> >
> > I will try and see if these will work for me.
> >
> > What I forgot to state, is that the concatenation must not turn the
> numbers
> > into text. The numbers must stay numbers. (I tried a TEXT(x,"00")) type
> > formula. It put everything together, but I had all text instead of text,
> > numbers, text.
> >
> > Bob
> >
> > "Dave Peterson" wrote:
> >
> > > in D2:
> > > =$a$1&text(b2,"0000")&$c$1
> > > dragged down
> > >
> > > Or maybe:
> > > in D2:
> > > =$a$1&text(row()-1,"0000")&$c$1
> > >
> > >
> > >
> > >
> > >
> > > sunslight wrote:
> > > >
> > > > I need a formula to concatenate A1,B#,C1; and place the result in D2,
> D3, D...
> > > >
> > > > A B C D
> > > > 1 dog100 0000 red ""
> > > > 2 0001 dog1000001red
> > > > 3 0002 dog1000002red
> > > >
> > > > A1: Does not change its location. It is an absolute reference.
> > > > It is in general format, a combination of text and a number. It is
> imported
> > > > that way.
> > > >
> > > > B2: is a custom format number of the form "0000". I have to keep all
> the
> > > > digits. It is derived from a formula to increment the # in the
> previous
> > > > column, by one.
> > > >
> > > > C3: Does not change its location. It is an absolute reference.
> > > > It is Text.
> > > >
> > > > What I want is to end up with D2, D3,.. a combination of text and
> value,
> > > > derived by combining A1,B...,C1.
> > > > Then copy the formula down the D column, so it will referentialy
> calculate
> > > > the next answer.
> > > >
> > > > Can someone help me put together text + numbers that increment?
> > > >
> > > > Thanks,
> > > > Bob
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
>
>

Either of Dave's formulas give you that facility

=$a$1&text(b2,"0000")&$c$1
dragged down

Or maybe:
in D2:
=$a$1&text(row()-1,"0000")&$c$1


--
HTH

Bob Phillips

"sunslight" <[email protected]> wrote in message
news:[email protected]...
> Thank you.
>
> Hopefully I can stay on line long enough this time so I can reply to your
> answers--my system died for several days, right after my last post.
>
> Ok, that's as I thought: Concatentaion takes text & numbers and makes a
TEXT
> string.
>
> You say I can strip out the numbers (text) and return them as numbers--I
> don't know how to do that.
>
> What I need is an alph numeric string, as:
>
> sky1blue2rain
> sky1blue3rain
> sky1blue4sun
> sky1blue5night
>
> After the initial entry, whichever digit I want, in this case, the second,
> is incremented and is a real number, not text.
>
> Is there a forumla for that?
>
> Or do I do the math, concatenate, then restore the (text)numbers to real
> numbers, which I can do, but i don't know how to put them back into the
> string.
>
> Hopefully, there will be an easy way and a formula that will let me mix
> modes, have an alphanumeric string, where the numbers can be manipulated.
>
> Thanks,
> Bob
>
>
> "CLR" wrote:
>
> > The result of a CONCATENATION is a TEXT string........
> >
> > From Help.....
> > CONCATENATE
> > Joins several text strings into one text string.
> > Syntax:
> > CONCATENATE (text1,text2,...)
> > Text1, text2, ... are 1 to 30 text items to be joined into a single
text
> > item. The text items can be text strings, numbers, or single-cell
> > references.
> >
> > Of course, concatenated numbers can be stripped back out of a string and
> > returned to number status.......
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "sunslight" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thank you both.
> > >
> > > I will try and see if these will work for me.
> > >
> > > What I forgot to state, is that the concatenation must not turn the
> > numbers
> > > into text. The numbers must stay numbers. (I tried a TEXT(x,"00"))
type
> > > formula. It put everything together, but I had all text instead of
text,
> > > numbers, text.
> > >
> > > Bob
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > in D2:
> > > > =$a$1&text(b2,"0000")&$c$1
> > > > dragged down
> > > >
> > > > Or maybe:
> > > > in D2:
> > > > =$a$1&text(row()-1,"0000")&$c$1
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > sunslight wrote:
> > > > >
> > > > > I need a formula to concatenate A1,B#,C1; and place the result in
D2,
> > D3, D...
> > > > >
> > > > > A B C D
> > > > > 1 dog100 0000 red ""
> > > > > 2 0001 dog1000001red
> > > > > 3 0002 dog1000002red
> > > > >
> > > > > A1: Does not change its location. It is an absolute reference.
> > > > > It is in general format, a combination of text and a number. It
is
> > imported
> > > > > that way.
> > > > >
> > > > > B2: is a custom format number of the form "0000". I have to keep
all
> > the
> > > > > digits. It is derived from a formula to increment the # in the
> > previous
> > > > > column, by one.
> > > > >
> > > > > C3: Does not change its location. It is an absolute reference.
> > > > > It is Text.
> > > > >
> > > > > What I want is to end up with D2, D3,.. a combination of text and
> > value,
> > > > > derived by combining A1,B...,C1.
> > > > > Then copy the formula down the D column, so it will referentialy
> > calculate
> > > > > the next answer.
> > > > >
> > > > > Can someone help me put together text + numbers that increment?
> > > > >
> > > > > Thanks,
> > > > > Bob
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> >
> >

Thanks Bob. Thank Dave and all.

Yes, this is doing the job for me now, almost. I don't know why it didn't
work before--anyway it is now (almost).

Again, I wasn't specific enough.

I thought I by keeping the data simple and it'd be easier, but it's not
working right.

I want the result field to be hyperlinks.

Thus, the entry info should have been this

A1 http://dog100
C1: .com

Putting that data in and using the formula, D2:
=$a$1&text(row()-1,"0000")&$c$1
the results are:

http://dog1000001.com
http://dog1000002.com
http://dog1000003.com

They look like hyperlinks, but aren't.

Excell isn't picking up the use of "http:"

I tried putting HYPERLINK into the formula, but couldn't get my using it, to
work.

The last question, then, is how do I get the result to be a hyperlink?
--we're so close

Thanks,
Bob


"Bob Phillips" wrote:

> Either of Dave's formulas give you that facility
>
> =$a$1&text(b2,"0000")&$c$1
> dragged down
>
> Or maybe:
> in D2:
> =$a$1&text(row()-1,"0000")&$c$1
>
>
> --
> HTH
>
> Bob Phillips
>
> > "CLR" wrote:
> >
> > > The result of a CONCATENATION is a TEXT string........
> > >
> > > From Help.....
> > > CONCATENATE
> > > Joins several text strings into one text string.
> > > Syntax:
> > > CONCATENATE (text1,text2,...)
> > > Text1, text2, ... are 1 to 30 text items to be joined into a single
> text
> > > item. The text items can be text strings, numbers, or single-cell
> > > references.
> > >
> > > Of course, concatenated numbers can be stripped back out of a string and
> > > returned to number status.......
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "sunslight" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Thank you both.
> > > >
> > > > I will try and see if these will work for me.
> > > >
> > > > What I forgot to state, is that the concatenation must not turn the
> > > numbers
> > > > into text. The numbers must stay numbers. (I tried a TEXT(x,"00"))
> type
> > > > formula. It put everything together, but I had all text instead of
> text,
> > > > numbers, text.
> > > >
> > > > Bob
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > in D2:
> > > > > =$a$1&text(b2,"0000")&$c$1
> > > > > dragged down
> > > > >
> > > > > Or maybe:
> > > > > in D2:
> > > > > =$a$1&text(row()-1,"0000")&$c$1
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > sunslight wrote:
> > > > > >
> > > > > > I need a formula to concatenate A1,B#,C1; and place the result in
> D2,
> > > D3, D...
> > > > > >
> > > > > > A B C D
> > > > > > 1 dog100 0000 red ""
> > > > > > 2 0001 dog1000001red
> > > > > > 3 0002 dog1000002red
> > > > > >
> > > > > > A1: Does not change its location. It is an absolute reference.
> > > > > > It is in general format, a combination of text and a number. It
> is
> > > imported
> > > > > > that way.
> > > > > >
> > > > > > B2: is a custom format number of the form "0000". I have to keep
> all
> > > the
> > > > > > digits. It is derived from a formula to increment the # in the
> > > previous
> > > > > > column, by one.
> > > > > >
> > > > > > C3: Does not change its location. It is an absolute reference.
> > > > > > It is Text.
> > > > > >
> > > > > > What I want is to end up with D2, D3,.. a combination of text and
> > > value,
> > > > > > derived by combining A1,B...,C1.
> > > > > > Then copy the formula down the D column, so it will referentialy
> > > calculate
> > > > > > the next answer.
> > > > > >
> > > > > > Can someone help me put together text + numbers that increment?
> > > > > >
> > > > > > Thanks,
> > > > > > Bob
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > >
> > >
>
>
>

Use the =hyperlink() formula, too:
=hyperlink($a$1&text(row()-1,"0000")&$c$1)
or
=hyperlink($a$1&text(row()-1,"0000")&$c$1,"Click me!")



sunslight wrote:
>
> Thanks Bob. Thank Dave and all.
>
> Yes, this is doing the job for me now, almost. I don't know why it didn't
> work before--anyway it is now (almost).
>
> Again, I wasn't specific enough.
>
> I thought I by keeping the data simple and it'd be easier, but it's not
> working right.
>
> I want the result field to be hyperlinks.
>
> Thus, the entry info should have been this
>
> A1 http://dog100
> C1: .com
>
> Putting that data in and using the formula, D2:
> =$a$1&text(row()-1,"0000")&$c$1
> the results are:
>
> http://dog1000001.com
> http://dog1000002.com
> http://dog1000003.com
>
> They look like hyperlinks, but aren't.
>
> Excell isn't picking up the use of "http:"
>
> I tried putting HYPERLINK into the formula, but couldn't get my using it, to
> work.
>
> The last question, then, is how do I get the result to be a hyperlink?
> --we're so close
>
> Thanks,
> Bob
>
> "Bob Phillips" wrote:
>
> > Either of Dave's formulas give you that facility
> >
> > =$a$1&text(b2,"0000")&$c$1
> > dragged down
> >
> > Or maybe:
> > in D2:
> > =$a$1&text(row()-1,"0000")&$c$1
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > > "CLR" wrote:
> > >
> > > > The result of a CONCATENATION is a TEXT string........
> > > >
> > > > From Help.....
> > > > CONCATENATE
> > > > Joins several text strings into one text string.
> > > > Syntax:
> > > > CONCATENATE (text1,text2,...)
> > > > Text1, text2, ... are 1 to 30 text items to be joined into a single
> > text
> > > > item. The text items can be text strings, numbers, or single-cell
> > > > references.
> > > >
> > > > Of course, concatenated numbers can be stripped back out of a string and
> > > > returned to number status.......
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > > "sunslight" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Thank you both.
> > > > >
> > > > > I will try and see if these will work for me.
> > > > >
> > > > > What I forgot to state, is that the concatenation must not turn the
> > > > numbers
> > > > > into text. The numbers must stay numbers. (I tried a TEXT(x,"00"))
> > type
> > > > > formula. It put everything together, but I had all text instead of
> > text,
> > > > > numbers, text.
> > > > >
> > > > > Bob
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > in D2:
> > > > > > =$a$1&text(b2,"0000")&$c$1
> > > > > > dragged down
> > > > > >
> > > > > > Or maybe:
> > > > > > in D2:
> > > > > > =$a$1&text(row()-1,"0000")&$c$1
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > sunslight wrote:
> > > > > > >
> > > > > > > I need a formula to concatenate A1,B#,C1; and place the result in
> > D2,
> > > > D3, D...
> > > > > > >
> > > > > > > A B C D
> > > > > > > 1 dog100 0000 red ""
> > > > > > > 2 0001 dog1000001red
> > > > > > > 3 0002 dog1000002red
> > > > > > >
> > > > > > > A1: Does not change its location. It is an absolute reference.
> > > > > > > It is in general format, a combination of text and a number. It
> > is
> > > > imported
> > > > > > > that way.
> > > > > > >
> > > > > > > B2: is a custom format number of the form "0000". I have to keep
> > all
> > > > the
> > > > > > > digits. It is derived from a formula to increment the # in the
> > > > previous
> > > > > > > column, by one.
> > > > > > >
> > > > > > > C3: Does not change its location. It is an absolute reference.
> > > > > > > It is Text.
> > > > > > >
> > > > > > > What I want is to end up with D2, D3,.. a combination of text and
> > > > value,
> > > > > > > derived by combining A1,B...,C1.
> > > > > > > Then copy the formula down the D column, so it will referentialy
> > > > calculate
> > > > > > > the next answer.
> > > > > > >
> > > > > > > Can someone help me put together text + numbers that increment?
> > > > > > >
> > > > > > > Thanks,
> > > > > > > Bob
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

--

Dave Peterson