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

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

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

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)



"andreashermle" wrote in message
...
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

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

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" wrote:

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
.

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

On 21 Apr., 16:58, "Rick Rothstein"
wrote:
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)

"andreashermle" wrote in message

...



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- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Rick,

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

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

On 21 Apr., 17:20, Mike H wrote:
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" wrote:
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
.- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Mike,

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