Dec 16 2021 01:28 PM
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.
Dec 16 2021 01:42 PM
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
Dec 16 2021 03:39 PM
You might also be able to achieve the result you require by changing the number format to 'text'.
Dec 16 2021 11:00 PM
Solution@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.
Dec 17 2021 05:55 AM
Dec 17 2021 05:58 AM
Dec 17 2021 05:58 AM
Dec 16 2021 11:00 PM
Solution@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.