How can I include quotations in a CONCATENATE operation?

Question

I've got an Excel file with a list of file paths that I need to perform actions on, via batch operation. I figured the best way to do this would be to write a CONCATENATE formula to put together the command for me, copy it down all the rows, and dump the column into a batch file.

However, some of the file paths contain spaces. For a batch command to work on these properly, I need to encase the path in quotes. However, any time I try to put the quotes into the CONCATENATE formula, they either don't make it into the end result or the cause the formula to error out. How can I resolve this?

For example, given:

What CONCATENATE formula would put the following into C2:

COPY "C:\My Files\*.*" "D:\My Backup\*.*" /Y

Answer 1

Option 1: You can use CHAR(34) to represent quotes. For your example:

=CONCATENATE("COPY ", CHAR(34), A2,"*.*",CHAR(34)," ",CHAR(34), B2,"*.*",CHAR(34), "/Y")

Option 2: You can also create a Name for Char(34) and use it instead of typing the formula each time you need quotes.

Example:

=CONCATENATE("COPY ", quot, A2, "*.*", quot, " ", quot, B2, "*.*", quot, "/Y")

Option 3: You can also enter a single quotation mark into a blank cell and just point to it in your formula.

Example:

=CONCATENATE("COPY ", F1, A2, "*.*", F1, " ", F1, B2, "*.*", F1, "/Y")

where F1 contains "

Answer 2

= "Copy """ & A1 & "*.*"" """ & B1 & "*.*"" /Y"