formula bar shows a ' before text

[Guest]

I know using the ' when the cell is formatted as text will enable a leading
0(zero) to show up. I was just told that before my time (thanks, glad to
know someone considers me young still) that the ' before text will make it
left justified. Well, that's kind of what is happening. We have a simple
spreadsheet with a column of numbers, a column of text and another column of
number (currency actually.) The column of text all had a ' before the text
when viewed in the formula bar. But it didn't show up in viewing the cells.
This probably wouldn't cause a problem normally except that it wasn't showing
this way on another person's computer which still normally wouldn't be a
problem. But this spreadsheet needed to be imported into an accounting
program. The accounting program wouldn't take it because of the ' in the
cells containing text. It didn't like it for some reason. Who knows. We
have had many many many problems with said accounting program. Any way, I
had accounting program support on the phone and lo and behold before I could
get my question out it worked. It was imported just fine. You would thing
my problem is over, right? Not really. Does anyone know why the ' was
there? We have a button for left justification and we couldn't get rid of
the '. If any of that made any sense, please respond. My main problem is
taken care of but if it were to happen again, I would like to know why and
how to fix it.

Thanks for your time.
Rose

 

[davesexcel]

all I can think of is that somebody from old school had something to do
with the spreadsheet, especially from the old Lotus days

 

[Guest]

The ' is probably there due to the original source of the data. I've often
gotten data from production systems that appears numeric but is in fact text
(the account numbers in our accounting system come to mind). If you need to
convert a bunch of such numbers, one easy approach is to enter a 0 in a blank
cell and copy (ctrl+C). Then highlight the cells you need to convert and
Edit > Paste Special, select the 'Add' operation and click ok. Adding 0 is
enough to convince Excel to change text representations of numbers into
numbers.
--Bruce

 

[Gord Dibben]

Rose

If you center the text does the ' change to a ^ ?

Try unchecking Tools>Options>Transition>Transition Navigation keys.


Gord Dibben MS Excel MVP

 

[Guest]

That did the trick. Thanks.

Rose

If you center the text does the ' change to a ^ ?

Try unchecking Tools>Options>Transition>Transition Navigation keys.


Gord Dibben MS Excel MVP