Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Excel error - remove some formatting | Excel Discussion (Misc queries) | |||
How to remove hidden grapics from copied web page pasted in Excel. | Excel Worksheet Functions | |||
how to remove label formatting (eg label to number) | Excel Worksheet Functions |