Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I delete hidden character in Excel?
Hi,
I have exported a table from Access 2000 to Excel 2000. However, all the fields have a hidden character in front which is ' I am unable to do any formulas as it doesn't recognise it as a number but has the number format. I can't get rid of it when doing a search and replace either. Can you help? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I delete hidden character in Excel?
Isa,
You could try using the CLEAN worksheet function. e.g. if your data is on Sheet1 create a new sheet and in Cell A1 of that sheet enter =CLEAN('Sheet1'!A1) and drag-copy that formula to an area the same size as the data on Sheet1. Having done that you may want to copy the data on your new sheet and pastespecial the values to get rid of the formulas. HTH Nick. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I delete hidden character in Excel?
Try a macro
Sub RemApostrophe() Dim Rng As Range Dim myCell As Range Set Rng = Selection For Each myCell In Rng.Cells myCell.Value = myCell.Value Next myCell End Sub press Alt + F11, click insertmodule and paste in the above, press Alt + Q select the range and press Alt + F8 and double click the macro name I don't think CLEAN will fix an apostrophe Another way might be to select the column, do datatext to column and press finish -- Regards, Peo Sjoblom "Isa" wrote in message ... Hi, I have exported a table from Access 2000 to Excel 2000. However, all the fields have a hidden character in front which is ' I am unable to do any formulas as it doesn't recognise it as a number but has the number format. I can't get rid of it when doing a search and replace either. Can you help? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I delete hidden character in Excel?
Hi Isa. You might also use the Value function. This will transform the text
you exported from Access to a number. Assume your data is in A1 to A100, in B1 type =Value(A1) and copy down to B100. Then copy B1:B100 and Paste Special - Values into A1:A100. HTH -- Sincerely, Michael Colvin "Isa" wrote: Hi, I have exported a table from Access 2000 to Excel 2000. However, all the fields have a hidden character in front which is ' I am unable to do any formulas as it doesn't recognise it as a number but has the number format. I can't get rid of it when doing a search and replace either. Can you help? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i delete a macro in Excel 2003 when delete isn't highlight | Excel Discussion (Misc queries) | |||
HELP - How to replace CRLF with a character withing an excel column | Excel Discussion (Misc queries) | |||
can't delete a cell content in excel | New Users to Excel | |||
sort column data with hidden columns - excel 2003 | Excel Discussion (Misc queries) | |||
Hidden page bracks in Excel should be deactivated | Excel Discussion (Misc queries) |