Excel is a great tool for presenting numbers, figures, and data. It is probably not the best option to manipulate the text, as there are programs that do it better.
However, this does not mean that Excel cannot manipulate text in a great way. One of the options that we have at our disposal for this is CONCATENATE, and it serves as a great tool for grouping the text. There are some other options as well, and we will show them in the text above.
Concatenate Double Quotation Marks
For our example, let’s that we have a short list of books that were published in 2023, with their respective authors:
It is clear that the book’s titles are not in quotation marks, and we will say that we need it to be. For the first book, we will use the simplest formula for concatenation (in cell C2):
|
1 |
=""""&B2&"""" |
When we insert it, the result will be the one that we need:
This formula connects the two double quotation marks at the beginning and at the end of the cell value in cell B2. The fact that we have four double quotation marks tells us that we actually have a pair of quotation marks within our string.
For the second book, we can use the CONCATENATE formula. We will insert the following formula in cell B3:
|
1 |
=CONCATENATE("""",B3,"""") |
We use the same logic as in the previous example, with the difference being that we use a proper CONCATENATE formula. We will get the same result:
We can also use the CHAR formula to get double quotation marks. CHAR formula, by its description, returns the character by the number from the list of characters set on our computer. The CHAR number for quotation marks is number 34, so our formula in cell C4 will be:
|
1 |
=CHAR(34) & B4 & CHAR(34) |
This will lead us to the same result as before:
Another way to get quotation marks is to use the REPT formula in combination with the CHAR formula. REPT function does what its name suggests- it repeats a certain character a specified number of times. We will simply add it to the previously shown CHAR formula, and will insert the following formula in cell C5:
|
1 |
=REPT(CHAR(34),1) & B5 & REPT(CHAR(34),1) |
And we will get:
For this example, we did not even need to add the REPT formula, but it is useful to see what it can be used for. If we have chosen to add another number after number 34 in our formula, for example, number 2, we would end up with two quotation marks.
For the last book, we can choose to repeat any of the options we used before.