Clean does not remove hidden formatting on a number
 

I want to copy the subject and date from the Outlook mail list window to an
excel file and sort them mail headings/date received and then find how many
occurrences of the unique id there is.
In the mail heading is a serial number e.g.
Subject Received
398053 (CLO-C) ABC SCP: TEXMEXL MX (Mexico) fr 2005-04-29 12:32
To copy the mail list to excel, I highlight the whole mail list, select copy
from the edit menu and then paste in to the excel sheet.
I then use LEFT(cell,6) to extract the 6 numbers form the subject to a new
column.
However there is some sort of hidden charecter as COUNT will not count the
digits. I tried to use CLEAN but no difference.
The only things that seem to work a
- open the cell, move the cursor in front of the first digt, pressbackspace,
return
or
- open the cell. Go to the formula bar, highlight the number and press enter
If I do this then the text is now a number and COUNT sees it.
I have tried seeing what the hidden charecter is but in Hex it doesn't show
either.
This seems to happen from several tools e.g. excel plus many SAP applications.

Any ideas how to automate the remvoal of what ever it is?

Chip Pearson has a nice addin that can help you determine each character in a
cell:
http://www.cpearson.com/excel/CellView.htm

If it turns out that you have spaces (or those non-breaking spaces), David
McRitchie has some code that will help clean this junk up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

MickF wrote:

I want to copy the subject and date from the Outlook mail list window to an
excel file and sort them mail headings/date received and then find how many
occurrences of the unique id there is.
In the mail heading is a serial number e.g.
Subject Received
398053 (CLO-C) ABC SCP: TEXMEXL MX (Mexico) fr 2005-04-29 12:32
To copy the mail list to excel, I highlight the whole mail list, select copy
from the edit menu and then paste in to the excel sheet.
I then use LEFT(cell,6) to extract the 6 numbers form the subject to a new
column.
However there is some sort of hidden charecter as COUNT will not count the
digits. I tried to use CLEAN but no difference.
The only things that seem to work a
- open the cell, move the cursor in front of the first digt, pressbackspace,
return
or
- open the cell. Go to the formula bar, highlight the number and press enter
If I do this then the text is now a number and COUNT sees it.
I have tried seeing what the hidden charecter is but in Hex it doesn't show
either.
This seems to happen from several tools e.g. excel plus many SAP applications.

Any ideas how to automate the remvoal of what ever it is?

--

Dave Peterson

MickF wrote...
....
398053 (CLO-C) ABC SCP: TEXMEXL MX (Mexico) fr 2005-04-29 12:32
....
I then use LEFT(cell,6) to extract the 6 numbers form the subject to a
new
column.
However there is some sort of hidden charecter as COUNT will not count
the
digits. I tried to use CLEAN but no difference.
....

There are no extra characters.

If you just have =LEFT(cell,6) in the formula, and you're using COUNT
to count the 'numbers' returned by these formulas, then Excel is
correctly returning 0. LEFT *ALWAYS* returns text, even if it returns
nothing but decimal numerals. COUNT won't include any text in its
result even if some of text cells contain only decimal numerals.

If you want the results of these LEFT calls to be treated as numbers,
then you need to convert them to numbers. Easiest would be

=--LEFT(cell,6)