Deleting the ' character

[scubadiver]

Hello,

In the cells of two columns I (unintentionally) have the ' sign (on the same
key as "@") preceding the text. When I do CTRL F it doesn't find this
character.

So how can I easily delete this character without going through each one
individually?

thanks

 

[Ron Coderre]

Try this:

Copy an EMPTY CELL

Select the range of text that has preceding apostrophes.

From the Excel Main Menu:
<edit><paste special>
....Check: Add
....Click: [OK]

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Mike H]

Right click the sheet tab, view code and psate this in and run it

Sub ApostropheKiller()
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
If c.HasFormula = False Then
c.Value = c.Text
End If
Next c
Application.ScreenUpdating = True
End Sub

Mike

 

[scubadiver]

That is brilliant!

thanks

Right click the sheet tab, view code and psate this in and run it

Sub ApostropheKiller()
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
If c.HasFormula = False Then
c.Value = c.Text
End If
Next c
Application.ScreenUpdating = True
End Sub

Mike

 

[Dave Peterson]

Instead of using:
c.Value = c.Text
I'd use:
c.Value = c.Value

Then if the cell's value is: 12345.12345, but is formatted to not display the
decimal places, the value wouldn't get truncated.