CODEWITHSUNDEEP
excelvbaconcatenation
Peter Grandjean
Peter Grandjean

Reputation: 3

How to concatenate in vba

I have approximately 100000 lines with data from different countries. I have to format the national IDs into different formats. Eg 123456789 should be 12.345-6789 My challenge is that if I use

cells(i,6).formula = "=left(SecId,3)"

It should be followed by &"." and so on

But this will not work.

Any suggestions ?

Upvotes: 0

Views: 315

Answers (1)

FunThomas
FunThomas

Reputation: 29652

The & you are providing in your code is used as concatenation operator within VBA, concatenating the strings "=left(SecId,3)" and ..

You want the & as part of the formula, so simply put it into the string. Now, VBA doesn't see the & as operator but simply as part of the string. It will then be part of the formula in the cell and Excel will interpret it.

cells(i,6).formula = "=left(SecId,3)&."

Of course you can use the & within VBA if you need to:

cells(i,6).formula = "=left(SecId,3)" & "&."

Now the first & is seen by VBA while the second one is part of a string and passed to Excel.

Upvotes: 2

Related Questions