Concatenate text and numbers?

[CLR]

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

 

[Guest]

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]

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

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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

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

 

[Guest]

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

 

[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!")