Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

enter image description here

What CONCATENATE formula would put the following into C2:

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

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

enter image description here

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 "

share|improve this answer
= "Copy """ & A1 & "*.*"" """ & B1 & "*.*"" /Y"
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.