Concatenate text and numbers?

  • Thread starter Thread starter CLR
  • Start date Start date
C

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
 
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
 
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
 
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
 
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 said:
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
Click to expand...
 
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
 
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!")
 
Back
Top