Put a single quotation mark in front of numbers in selected cells

[andreashermle]

Dear Expert:

I need to convert numbers to text and put a tick (') mark/single
quotation mark in front of every one of them.

I would like to have a macro solution and the macro should work ONLY
on selected cells.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

 

[Rick Rothstein]

This does exactly what you asked for...

Sub AddApostrophe()
Dim C As Range
For Each C In Selection
C.Replace C.Value, "'" & C.Value
Next
End Sub

However, you do not need to put an apostrophe in front of each number to
convert it to text, you can use this simpler macro to change the Cell Format
of the selected cells to Text instead...

Sub ConvertToText()
Selection.NumberFormat = "@"
End Sub

 

[Mike H]

Hi,

Maybe this

Sub sonic()
For Each c In Selection
If IsNumeric(c) Then
c.Value = "'" & c.Value
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[andreashermle]

This does exactly what you asked for...

Sub AddApostrophe()
  Dim C As Range
  For Each C In Selection
    C.Replace C.Value, "'" & C.Value
  Next
End Sub

However, you do not need to put an apostrophe in front of each number to
convert it to text, you can use this simpler macro to change the Cell Format
of the selected cells to Text instead...

Sub ConvertToText()
  Selection.NumberFormat = "@"
End Sub

--
Rick (MVP - Excel)










- Zitierten Text anzeigen -

Hi Rick,

thank you very much for your terrific help. Both codes are just fine.
Regards, Andreas

 

[andreashermle]

Hi,

Maybe this

Sub sonic()
For Each c In Selection
    If IsNumeric(c) Then
        c.Value = "'" & c.Value
    End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.









- Zitierten Text anzeigen -

Hi Mike,

thank you very much for your professional help. It works fine.
Regards, Andreas