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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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")

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

=A1&""

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.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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

screenshot
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.

dropdown

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
aikimarkCommented:
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
    Next
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.

Thanks
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.