Removing the Apostrophe
 

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

Removing the Apostrophe
 

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

"Rob" wrote:

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

Removing the Apostrophe
 

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

Removing the Apostrophe
 

Kassie,

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

Rob

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

"Rob" wrote:

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

Removing the Apostrophe
 

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

Removing the Apostrophe
 

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

Removing the Apostrophe
 

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

Many thanks, rob
"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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