Concatenate cells into Text Box, Excel

[ran13]

How do I concatenate cells into a text box in Excel without first putting the concatenation into another cell?

I'd also like to extract text from a text box into separate cells.

 

[zliazmei]

Ran

May be my uderstanding of your question is not correct, but if it is try this:

Let say:
in A1 you typed b=
in B1 you have 171.234
In C1 you have 200.2356

the string =A1&Fixed(B1,1,1)&"+"&FIXED(C1,3,1)&"
will give in your cell the result:
b=171.2+200.236 as a text

Hope it helps

Zmei

 

[ran13]

You're right; your example will give you that result in a cell. However, I want the result to be in a text box, not another cell.

 

[yakpol]

ran13,
To automate textbox you have to use VBA, there's no a way around it. The following function will update the textbox every time you change the cell values in the worksheet. Place it in the worksheet vba module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = ActiveCell
    ActiveSheet.Shapes("Text Box 1").Select
    Selection.Characters.Text = Range("B6") & Range("C6")
    rng.Select
End Sub

Hope it helps!

 

[ran13]

I appreciate your help.

Thanks,

Roger

 

[ivymike]

first do what zmei suggested:
in A1 you typed b=
in B1 you have 171.234
In C1 you have 200.2356
the string =A1&Fixed(B1,1,1)&"+"&FIXED(C1,3,1)&"
will give in your cell the result:
b=171.2+200.236 as a text



then try this:

create the textbox
select the textbox by clicking on it once
in the equation bar, type your equation (such as =A1&Fixed(B1,1,1)&"+"&FIXED(C1,3,1)&"

lemme know if that works. It works for me in xl2k.

 

[ivymike]

nevermind, it only seems to work using a single cell of text (so you'd have to combine your strings elsewhere first).

 

[pmover]

ran13,
ivymike response will work, with another technique.
1) concatenate all the input data into another cell
2) create textbox
3) with textbox selected (not interior of textbox, but textbox border), press the "=" and then the cell with all the concatenated cells.
pmover