A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I delete hidden character in Excel?



 
 
Thread Tools Display Modes
  #1  
Old November 15th 05, 03:50 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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.
Ads
  #2  
Old November 15th 05, 04:04 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old November 15th 05, 04:19 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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 insert>module 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 data>text 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  
Old November 15th 05, 04:29 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i delete a macro in Excel 2003 when delete isn't highlight Abel Excel Discussion (Misc queries) 2 September 13th 05 04:09 AM
HELP - How to replace CRLF with a character withing an excel column Dino Buljubasic Excel Discussion (Misc queries) 1 August 25th 05 07:49 PM
can't delete a cell content in excel Mark B New Users to Excel 3 May 6th 05 06:42 PM
sort column data with hidden columns - excel 2003 nanimadrina Excel Discussion (Misc queries) 2 April 26th 05 08:27 PM
Hidden page bracks in Excel should be deactivated Tonywww Excel Discussion (Misc queries) 0 December 21st 04 06:21 PM


All times are GMT +1. The time now is 12:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.