concenate or vba or sql

  • Thread starter Thread starter avivit
  • Start date Start date
A

avivit

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
 
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
 
Back
Top