I have a list of entries that I want to convert to be surrounded by double quotes (") The content of these entries can then be used to apply to formulas further used in the book I am working with. To perform the quote addition, the only way I can think of is to generate a new column, use CONCATENATE("""",(REF),"""") and save as a csv. Reload the csv and delete the column that does not have the quotes. I was hoping there was a command that would enable me to achieve this without exiting as doing so also only saves the sheet I am working on and not the book.

Hi @Richard Ehlers You may try the Format Cell. Select and right-click the data > Format Cell > Custom > Enter "''"@"''" into the Type textbox. If the steps does not meet your request, you may need the help of VBA code or 3rd-party add-in. If the answer is helpful, please click " Accept Answer " and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Make formula changes but save the results as text in the same cell

Accepted answer

Emily Hua-MSFT 27,666 Reputation points

2023-03-15T08:08:00.3866667+00:00
Hi @Richard Ehlers

You may try the Format Cell.

Select and right-click the data > Format Cell > Custom > Enter "''"@"''" into the Type textbox.

If the steps does not meet your request, you may need the help of VBA code or 3rd-party add-in.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.

1 person found this answer helpful.
Richard Ehlers 20 Reputation points

2023-03-15T21:54:53.8466667+00:00

Thank you for this suggestion Emily.

I understand the principle of it and certainly it would meet my requirement.

For some reason, the Excel I am using is not applying and keeping the new Custom entry.

It is as if my system is not allowing these types of additions to be made.

Could this be an administrator restriction within my organisation?

Richard Ehlers 20 Reputation points

2023-03-16T04:59:30.29+00:00

It should be noted that the "''"@"''" reference was confusing - I thought it was 3 double quotes until I put it into a document where it could be better read :(

Many thanks for your assistance with this.

It is truly appreciated.

Richard Ehlers 20 Reputation points

2023-03-16T23:56:16.29+00:00

Hi @Emily Hua-MSFT

I did check the content of the Notepad ++ file after doing this and there were some entries which simply showed as #####################.

This is not something that I anticipated or require to occur.
I have no idea why this might have occurred and if you have any input it would be appreciated.

Apologies for not referencing this check after running the initial process.

Richard Ehlers 20 Reputation points

2023-03-17T05:11:39.9533333+00:00

The issue appears to be that there is perhaps a combination of characters which might be perceived as 'special' and the @ only references up to the special character.

Even when this does occur, the " intended to surrounded the cell content is not occurring.

The content of the cell, regardless of what is present, requires " at the beginning and " at the end.
This is as far as my thinking takes me. I have been investigating other potential options but the result always seems to be the same.

Emily Hua-MSFT 27,666 Reputation points

2023-03-17T09:32:38.23+00:00

Hi @Richard Ehlers

Thanks for your details, I understand your needs.

As I said as above, you need the help of VBA code, such as following:

Sub AddQuote() Dim myCell As Range For Each myCell In Selection myCell.Value = Chr(34) & myCell.Value & Chr(34) Next myCell End Sub

I suggest you back up your workbook first.

An then press Alt + F11 to open VBA editor, copy the code into 'ThisWorkbook'.

Then please go back to worksbook, select the cells that you want to add double quotes.

Press Alt + F8, run 'ThisWorkbook.AddQuote' macro to have a check.

Please note, as the Excel tag focuses more on general issue of Excel, if you have further issues of VBA macro, I would suggest you post a new thread on Q&A with tag 'Office Development'.

Thanks for your understandings.

Richard Ehlers 20 Reputation points

2023-03-19T22:30:57.4233333+00:00

Many thanks @Emily Hua-MSFT
This is the perfect result.
I need to tweak it a little to get the final result that I need for my scenario however, it has resolved the issue that I have and I am most grateful

Emily Hua-MSFT 27,666 Reputation points

2023-03-20T02:45:10.5433333+00:00

Hi @Richard Ehlers

Many thanks for your feedback on my replies.

Any general issues of Office, welcome to discuss with us on Q&A forum.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Make formula changes but save the results as text in the same cell

0 additional answers

Your answer