Old problem resurfaces in new guise

[Cloudfall]

I once posted the topic "When is an Empty Cell not Blank?" and received
an answer from Dave Peterson as to the reason why an empty cell may not
necessarily contain nothing. He wrote as follows.

*If you had formulas that evaluated to "" and then converted to values,
you can see a single apostrophe in the formula bar of one of those
cells if you toggle this setting: Tools|Options|Transition tab|check
the 'transition navigation keys' box (uncheck after you're done
checking.) *

In order to 'blank' the 'empty' cells (and eliminate the single
apostrophe) I used the technique (not recommended if you have formulas)
selection.value = selection.value (as there is only string data in this
column).

This works for empty cells. It does not work if you have string data in
the cells.

How then can I 'clean' the single apostrophe from the data without
damaging the information in these cells? This single apostrophe is
preventing my replacing blanks between characters in these cells with
nothing (" " with ""). We've just received a file of information from a
client and my program is rejecting the ABN information because I can't
make the string of numbers into a numeric field. It isn't a drama at
the moment but it could be in the future.

Thanking all respondents now to my 'Friday 5pm fault' and have a good
weekend. I'm out the door now.

 

[Roger Govier]

Hi Sydney

Are you sure that you have the single apostrophe as the problem?
If it is, then entering
=--(A1) would return a numeric value.
If it returns #Value then it is more likely that the numeric data has the
non breaking space Char(160) appended to it.

Take a look at David McRitchie's Trimall function for help on how to
eliminate this.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Regards

Roger Govier

 

[Guest]

Try this tiny macro:

Sub tickout()
Dim r As Range
For Each r In Selection
r.Value = r.Value
Next
End Sub

1. apostrophe with text becomes text
2. apostrophe with formula becomes formula
3. apostrophe with number becomes num

 

[Cloudfall]

Hi Roger

You are correct! The single apostrophe wasn't the problem. All cells
had the single apostrophe (which I only realised after you raised this
issue) but only some cells wouldn't allow allow a replace of " " with
"". I inserted the following code into my program and it seems to work
(although now I've somehow introduced another bug, but that's another
story):

Selection.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByColumns, MatchCase:=False

I got this code from the internet site you recommended. Thank you very
much for your help and I hope you have a good life.

 

[Cloudfall]

Hi Gary's Student

The apostrophe wasn't the problem. However, your macro above performs
the same operation as selection.value = selection.value which only gets
rid of the single apostrophe in "empty" cells in my version of Excel
2000. Replacing spaces with zero length strings in the Australian
Business Number resulted in a numeric value without the single
apostrophe. Replacing the non breaking space character (&nbsp with
an empty string also resulted in a numeric value without the single
apostrophe. So, performing some kind of processing of the data in
"single apostrophe cells" seems to get rid of the single apostrophes.

Thank you for responding to my post and have a good one.

 

[Roger Govier]

Hi Sydney

You're very welcome.
I fell foul of this blessed Char(160) some while ago, and as usual found the
solution through helpful members of these NG's.

Regards

Roger Govier

 

[Cloudfall]

Roger,

Thanks again.

Cheers.