Removing the Apostrophe

[Rob]

Hi,

I weekly receive a spreadsheet that's be created from a database export and
where there is no data (text or numbers), the cell contains an apostrophe.

What I would like to do, is to check whether the cell only contains and
apostrophe and if so, remove it. The range of data varies from week to week
so I'm guessing I need to select the range or somehow check the area used
and run some VBA code.

I can recall something previously on the MS forums but searching doesn't
find anything.

Thanks in advance. Rob

 

[kassie]

It sounds as if only empty cells contain an apostrophe? If so, do a Find on
the apostrophe, and Replace All with nothing

 

[Bob Phillips]

Public Sub Test()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
cell.Value = cell.Value
End If
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Rob]

Kassie,

Find doesn't work, placing ' in find and leaving Replace blank prompts
saying noting to find.

Rob

 

[Rob]

Bob,

Thanks that removed the apostrophe however, it also has changed some data
for example '000045 becomes 45 whereas I need it to remain as a label as
there's a lookup formula that uses this.

Is there perhaps a second test to see it the length of the contents is
greater than zero. I try changing your code to use AND Len() but to no
avail.

Thanks, Rob

 

[Bob Phillips]

The Len returns 0 so you have to use that creatively <G>

Public Sub Test()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
If Len(cell.Value) = 0 Then cell.Value = cell.Value
End If
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Rob]

Bob, Brilliant, works a treat, easy when you know how!

Many thanks, rob