Macro to change a number to text

  • Thread starter Thread starter Lori F
  • Start date Start date
L

Lori F

I'm having trouble trying to get this done. Just
recording the keystrokes to put in the apostrophe isn't
working. Is there a statement to be used in the macro to
accomplish this?

Thanks in advance,
Lori
 
Hi Lori,

The following code will put a ' before each value in the selected cells:

Sub MakeText()
Dim rng As Range

For Each rng In Selection
With rng
.Value = "'" & CStr(.Value)
End With
Next rng
End Sub

Keep in mind that this will destroy formulas, so it should only be used on
cells that don't contain formulas. Here's how you can help protect against
that:

Sub MakeText()
Dim rng As Range

For Each rng In Selection
With rng
If Not .HasFormula Then .Value = "'" & CStr(.Value)
End With
Next rng
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
cell = "'" & cell.text

that's double quote, single quote, double quote

There are other ways, but this was the way you asked the question.

--
 
Thank you again, Jake! I'm not worried about the
formulas - the macro will only be used on raw data.

Lori
-----Original Message-----
Hi Lori,

The following code will put a ' before each value in the selected cells:

Sub MakeText()
Dim rng As Range

For Each rng In Selection
With rng
.Value = "'" & CStr(.Value)
End With
Next rng
End Sub

Keep in mind that this will destroy formulas, so it should only be used on
cells that don't contain formulas. Here's how you can help protect against
that:

Sub MakeText()
Dim rng As Range

For Each rng In Selection
With rng
If Not .HasFormula Then .Value = "'" & CStr(.Value)
End With
Next rng
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Lori said:
I'm having trouble trying to get this done. Just
recording the keystrokes to put in the apostrophe isn't
working. Is there a statement to be used in the macro to
accomplish this?

Thanks in advance,
Lori
Click to expand...

.
Click to expand...
 
Thank you so much, David! That should help a lot.

Lori
-----Original Message-----
cell = "'" & cell.text

that's double quote, single quote, double quote

There are other ways, but this was the way you asked the question.
Click to expand...
message news:[email protected]...
 
Back
Top