Hi,

1. I would like to concenate some columns in excel xp, automatically
I'll explain:

I get an excel file once a month, and would like the result of on
column to be a join of some other columns but with an added characte
or a string in between.
Is there an elegant way to do so, without the need to enter th
string/character in a seperate column and make a regular concenate?

One row For example: col A contains the value: 123; COL B contains 45
; col C: 789; col d: 10.
I'd like col d to join these columns, with something in between
"123$aa456 of 789 10"

Can I enter in "concenate", not only col name, but also a string.
Also, if I need some spaces instead of just a string in between
Possible? How?

2.My second problem:

How can I do this automatically?
I'd like to create a bottun or something similar, that once pressing o
it, this concenation will take place.
Macro? Is it possible to use sql in excel?Is vba possible to use? how?
Myabe it's not "concenate" that I need to use(?)

Any help will be most welcome.
Thank

--
Message posted from http://www.ExcelForum.com

CONCATENATE()

= A1 & " plus " & B1 & " = " & (A1 + B1)

in code
Range("C2").FormulaR1C1 = _
"= R2C1 & "" plus "" & R2C2 & "" = "" & (R2C1 + R2C2)"

This demonstrates how to set a formula in C2 to show
values from A2 and B2 as well as text.

HINT:
Often using concatenation is made simpler by having the
text pre-defined with placeholders for the varying text.
For example, we want to set a cell with this text
Add: 14 plus 21 = 35
but the numbers 14,21 and 35 will vary depending on the
cells pointed to.
Use a text string with place holders. In the following
code, I have a text string with place holders, or tokens
which are %A,%B and %C

Sub codeit()
Dim text As String
Dim Total As Double
text = " Add: %A plus %B = %C"

text = Replace(text, "%A", Cells(1, "A").Value)
text = Replace(text, "%B", Cells(1, "B").Value)
Total = Cells(1, "A").Value + Cells(1, "B").Value
text = Replace(text, "%C", Total)
Range("C1").Value = text

End Sub

Here, the tokens are replaced by the cell values.
You should practice with this and you'll see that it is
quite easy to automate.

the method used here is quite common...in C we had printf
() and this is carried through to even C#
How, well placeholders.
Our text would be
text = " Add: {0} plus {1} = {2}"
val1 is the first cell value.
val2 is the second cell value
val12 is val1+val2
our printf is
printf(text,val1,val2,val12)


Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hi,

1. I would like to concenate some columns in excel xp,
automatically.
I'll explain:

I get an excel file once a month, and would like the
result of one
column to be a join of some other columns but with an
added character
or a string in between.
Is there an elegant way to do so, without the need to
enter the
string/character in a seperate column and make a regular
concenate?

One row For example: col A contains the value: 123; COL
B contains 456
; col C: 789; col d: 10.
I'd like col d to join these columns, with something in
between:
"123$aa456 of 789 10"

Can I enter in "concenate", not only col name, but also
a string.
Also, if I need some spaces instead of just a string in
between.
Possible? How?

2.My second problem:

How can I do this automatically?
I'd like to create a bottun or something similar, that
once pressing on
it, this concenation will take place.
Macro? Is it possible to use sql in excel?Is vba
possible to use? how?
Myabe it's not "concenate" that I need to use(?)

Any help will be most welcome.
Thanks


---
Message posted from http://www.ExcelForum.com/

.