Hi Martina,
The leading apostrophe is not hurting anything, it is
an indicator that you have a text value. It is not seen
in macro. You will only see it at the formula bar.
The following macro will convert a selection to the formatted
value of the cell as a text string.
You would format the column as Text before running
the macro. (however the macro will format within the selection)
Sub AS_TEXT()
'DMcRitchie, code/join.txt, 2001-01-23, programming
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Make_A_Break 'allow change while running
On Error Resume Next 'In case no cells in selection
Dim vString As String
Dim cell As Range
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
vString = cell.Text
cell.NumberFormat = "@"
cell.Value = vString
Make_A_Break = DoEvents 'yield to system tasks
Next cell
'simplify formatting
Selection.NumberFormat = "@"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
If not familiar with installing and using macros, see
http://www.mvps.org/dmcritchie/excel/getstarted.htm