Excel - leading hidden apostrophe to indicate text

In Excel - how can I create a formula that will attach a leading apostrophe in front of numeric values to transform it into all cells in the column into text.

I have tried

= " ' " & A1

without success.

Thank you.
exp vgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
What's your aim? Can you post a sample workbook, showing before and after?

Generally to convert numbers to text, with a formula, you would use the TEXT function, but that requires a specified format...

    =TEXT(A1, "0.00")

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You can use a formula like


That will return a number stored as text. If you then copy and paste special as values it will still be a number stored as text, but it does not show a ' sign in the cell.

cheers, teylyn
exp vgAuthor Commented:
The aim is to have the hidden apostrophe

Example - when the small green triangle appears in the corner left to indicate a drop down to convert text to numbers if needed.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Consider this screenshot.

The formula in B1 is =A1&"" copied down. Then B1 to B5 have been copied and pasted into column C

The green triangle appears (if your Excel Options are configured to show it) and the option to convert to number appears when the dropdown is clicked.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
you need to format the column as text before populating it with data
Anne TroyEast Coast ManagerCommented:
So try using VBA instead.

Sub AddApost()
    For Each currentcell In Selection
        'Prevents inserting apostrophes in blank cells.
        If currentcell.Formula <> "" Then
            currentcell.Formula = "'" & currentcell.Formula
        End If
End Sub

Open in new window

Rob HensonFinance AnalystCommented:
The green triangle will only show after the values have been pasted as text.

A formula in the cell will not show as an error, even if said formula is generating what would normally be seen as an error such as Number stored as text.

Rob H
exp vgAuthor Commented:
Thank you everyone.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.