Excel copy contents from multiple cells adding quotations

0
votes

I'm trying to combine contents from multiple cells in excel. Here is an example of what i'm wanting to do:

(Cell1): John Doe

(Cell2): 5950

(Cell3): Autofill with "John Doe" XXXXXX5950

I know i can combine the cells using the formula =&[Cell1]&" XXXXXX"&[Cell2]

But I want it to put the " " on each side of the name, but the code won't except it since part of using the formula is using the " symbol. Any help on this?

2
excelms-officeformula

2 Answers

1
votes

Use two double-quotes inside a string to put a double-quote:

=""""&[Cell1]&""" XXXXXX"&[Cell2]
1
votes

You can escape the " character in VBA by using double quotes. 

Range("A1").Formula = """" & Range("B1").Value & """ XXXXXX" & Range("C1").Value

Or you can also use ASCII character 34 as well.

Range("A1").Formula = Chr(34) & Range("B1").Value & Chr(34) & " XXXXX" & Range("C2").Value