How-to Show an Apostrophe at the Beginning of a Text String in Excel

I saw that an Excel user was having a problem when creating his VBA code and you may run into this problem when you are trying to display text in your Excel Spreadsheet.  Here was his post:


Want Apostrophe before text to show in Data validation drop down list

I currently have a list for a data validation drop down that has Range names in it. when the Scope of a range is within the worksheet, the name comes in as ‘Graph 2’!myBarGraph1. But in my dropdown it looks like Graph 2′!myBarGraph1‘Graph 2’!myBarGraph1. My question is first can I get my list to show the apostrophe at the beginning of my name; if I can’t, then if I run a macro pulling that named range will I need to add an apostrophe with code?


To recap the his problem.  He wanted to send this text string to a calculation:

‘excel worksheet name’!excel_named_range

However, every time he put this in his VBA code, he would would get an error.

Turns out that Excel would ignore the first (left most) single quote ‘ .

So how can we solve this problem in your Excel Worksheet?

So what we want to do is show a single quote in a worksheet cell.  Like this:image

It is a pretty quick fix.  But this Expert Excel User didn’t know how to do it.  Lets show you how.

If you type in =SUM(7,56,22) in a cell, you will see this: image

If you type in ‘=SUM(7,56,22) in a cell, you will see this: image

If you type in ”=SUM(7,56,22) in a cell, you will see this: image

So that is the trick, you just need to add an additional single quote before the first single apostrophe.

So, how did it work out for the Excel User?

“That worked perfectly!!! Here is a snip of the code I ended up with:


.  For Each NamedRange In ActiveWorkbook.Names
.      NmRng = NamedRange.Name
.      Set NewRow = Table.ListRows.Add(AlwaysInsert:=True)
.           If Left(NmRng, 1) = “‘” Then NmRng = “‘” & NmRng

.       NewRow.Range(1, 1).Value = NmRng
.   Next NamedRange”


Cool.  So glad to help and now you know this easy Excel fix.

Video Demonstration:

Here is a super-short video showing you this technique.