Forum Discussion
apostrophe
how can i insert an apostrophe in front of numbers in a column so that they are now recognized as text. I have an entire column and can't seem to use concat or other functions to accomplish this.
TonyA730 Alternatively, just use "Text to Columns" (TTC) on the Data ribbon. Select the range of numbers (column wise) you want to convert to text. Press the TTC button. "Next", "Next" (i.e. change nothing in steps 1 and 2. Then in step 3 of 3, select Text as the data type. Press Finish.
The numbers will now be texts (left aligned), no apostrophes needed and no need to format the cells as text either.
6 Replies
TonyA730 Alternatively, just use "Text to Columns" (TTC) on the Data ribbon. Select the range of numbers (column wise) you want to convert to text. Press the TTC button. "Next", "Next" (i.e. change nothing in steps 1 and 2. Then in step 3 of 3, select Text as the data type. Press Finish.
The numbers will now be texts (left aligned), no apostrophes needed and no need to format the cells as text either.
- Oh my - thank you - this was sending me over the edge wasting time... I tried every formula I could dream of. LOL
Thanks again!
You might also be able to achieve the result you require by changing the number format to 'text'.
- Thank you very much for taking time to reply. However this doesn't work in terms of using it in vlookup. It isn't found with compairing to another text number with the apostrophe i.e. '123456
Select the range, then run the following macro:
Sub Apostrophe() Dim rng As Range Application.ScreenUpdating = False For Each rng In Selection If rng.Value <> "" Then rng.Value = "'" & rng.Value End If Next rng Application.ScreenUpdating = True End Sub- Thank you very much for taking time to help! It is appreciated.
