Excel: Leading apostrophe in cell

[norty303]

Hi All

I've been provided a workbook where all of the cells have a leading apostrophe when viewed in the formula bar although these do not show actually in the cell in the sheet. Performing a search does not find these. Could this be that the file was a delimitied file originally with ' as a separator?

Anyone encountered this b4? How/why does it occur?

 

[JohnAcc]

Do the formula's work ?

Rgds, John

 

[SkipVought]

Hi,

The leading apostrophy is an indicator of Text. It is the only way that a Number be treated as character digits.

It does not affect the data in the cell IF the data is to treated as text.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[norty303]

All of the cells are formatted as General and the ' exists even when the cell contains only text and no numbers. There are no formulas as the data is query results from an unknown database.

 

[JohnAcc]

If you NEED to get rid of the leading apostrophe then in a seperate sheet enter the formula:

=+Sheet1!A1 ( Assuming the data is in sheet 1 ) in cell A1 fo the new worksheet.

Copy this formula so you have an exact match of the worksheet and then copy and paste special the whole new sheet as values.

You should then be able to refer to numbers / text seperately.

Rgds, John

 

[JohnAcc]

To add further, if you want the cell data to show as numeric a text data type then run the following macro:

Sub ConvertToNumber()
For Each c In Range("A1:M500&quot ' your range required
If IsNumeric(c) Then c.Value = CLng(c)
Next
End Sub

Hope this helps,

Rgds, John

 

[SkipVought]

Why are you bothering?

This is a tempest in a teapot!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

And is very easily fixed......
Tools>Options>Transition
untick Transition Navigation Keys

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[norty303]

Thanks everyone for the input. I'm not actually too fussed about it but as this data is going to be uploaded to an Oracle table I wanted to ensure that if I left it be, there wouldn't be too many problems....