formula bar shows a ' before text

[Rose]

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,
you may have to highlite the range, goto edit find enter '
replace with blank

[bpeltzer]

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

"Rose" wrote:

> 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
>

[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

On Wed, 1 Mar 2006 14:58:27 -0800, "Rose" <[email protected]>
wrote:

>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

[Rose]

That did the trick. Thanks.

"Gord Dibben" wrote:

> Rose
>
> If you center the text does the ' change to a ^ ?
>
> Try unchecking Tools>Options>Transition>Transition Navigation keys.
>
>
> Gord Dibben MS Excel MVP
>
> On Wed, 1 Mar 2006 14:58:27 -0800, "Rose" <[email protected]>
> wrote:
>
> >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
>
>