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

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

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
 
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
 
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.
 
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 -
Click to expand...

Hi Rick,

thank you very much for your terrific help. Both codes are just fine.
Regards, Andreas
 
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 -
Click to expand...

Hi Mike,

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