VBA & Macro Problems

How to Convert Straight Quotes to Curly Quotes using Excel VBA Macros

Huma Ashraf Ch Oct 22, 2026 868 views

Question details

The user wants to automatically convert straight quotation marks to curly (smart) quotes in Excel using a macro, as Excel lacks Word's built-in AutoFormat functionality.

How to Convert Straight Quotes to Curly Quotes using Excel Macros

Product: Microsoft Excel
Device & OS: not provided
Scenario: Formatting text strings within spreadsheet cells that contain straight quotation marks which need to be typographically correct.
Observed behavior: Excel does not automatically convert straight quotes to curly quotes as you type. Using the standard Find and Replace tool often results in mismatched opening and closing quotation marks.

Before you start

Before running any VBA macros, ensure you have saved a backup copy of your workbook, as actions executed by a macro cannot be undone using the standard Undo shortcut.

Solution 1Recommended

Use a Custom VBA Macro for Sequential Quote Conversion

A custom VBA script can systematically identify the first straight quote as an opening curly quote and the second as a closing curly quote within each cell.

This is the most reliable method for cells containing simple quote pairs. The logic manually replaces the standard quote character code with the specific typographical character codes for open and close quotes.

Open the VBA Editor

Press Alt + F11 to open the Microsoft Visual Basic for Applications window in Excel.

Insert a New Module

Navigate to the top menu, click 'Insert', and select 'Module' to create a blank script window.

Write the Conversion Logic

Write a script that loops through your selected cells. Use the VBA InStr function to find Chr(34) (straight quote). Replace the first instance with Chr(147) (opening curly quote) and the second instance with Chr(148) (closing curly quote).

Run the Macro

Close the VBA editor, highlight the range of cells you wish to correct, press Alt + F8, select your new macro, and click 'Run'.

Macro Button Automation: You can insert a Form Control button from the Developer tab and assign this macro to it, allowing you to quickly format selected cells with a single click in the future.

Solution 2

Utilize the Word Object Model via VBA

Leverage Microsoft Word's built-in AutoFormat capabilities by calling the Word Object Model from Excel.

Solution 3

Use Excel's Find and Replace Feature (Manual Workaround)

A quick manual workaround for occasional corrections, suitable when you only have a few quotes to fix.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Formatting and Macro Support

If you frequently struggle with missing AutoFormat features or complex macro binding issues in Microsoft Excel, WPS Office provides a lightweight, highly compatible, and free alternative. WPS Spreadsheet fully supports VBA execution and advanced formatting in a familiar interface.

Fully compatible with Microsoft Excel file formats (.xlsx, .xlsm, .csv)Lightweight application that uses minimal system resourcesFamiliar user interface requires zero learning curveBuilt-in macro environment for automating repetitive tasks seamlessly

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't Excel automatically change straight quotes to curly quotes like Word does?

Unlike Microsoft Word, Excel is optimized for data analysis, formulas, and calculations where exact character codes are crucial. 'Smart quotes' can break formulas and programming scripts, so Excel disables this auto-formatting by default.

What are the ASCII character codes for straight and curly quotes in VBA?

In VBA, the standard straight double quote is represented by Chr(34). The typographical opening curly quote is Chr(147), and the closing curly quote is Chr(148).

Can I assign my quote conversion VBA macro to a button on my worksheet?

Yes. You can insert a shape or a Form Control button from the Developer tab, right-click the object, select 'Assign Macro', and choose your script. This allows you to select cells and convert quotes with a single click.

Is it possible to use standard Find and Replace for smart quotes without a macro?

Yes, but with limitations. Standard Find and Replace cannot distinguish context, so it will replace all straight quotes with whichever curly quote (opening or closing) you put in the 'Replace with' field, which causes mismatched pairs if you use 'Replace All'.

Excel & Data Problems