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" wrote in message
...
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

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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 a

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" wrote in message
...
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 a

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" wrote in message
...
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