Always have apostrophe

[snax500]

In Excel2000, is there a way to force an apostrophe in front of any
characters inputted into a cell. For example, if someone inputs 100
the cell will really be '100. I don't want to just use Format-Text, I
want an apostrophe added to anything input.

Thanks

 

[Earl Kiosterud]

Snax,

First of all, if you want the apostrophe to shoe, you'd need to put two apostrophes, since
it's the first character, because Excel uses the first to coerce it to text. It doesn't
show.

If the apostrophe is to coerce it to text (you don't want it to show), you could first
format the cells for text (Format - Cells - Number tab, Text.

I think he only way to have an apostrophe(s) automatically inserted is with a macro. We can
write you one if you're interested.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[snax500]

All that people would be inputting in is numbers. I just want there to
be a leading apostrophe on the numbers they input. I don't want to use
format-text because I am using a macro where the data has to be
excactly the same with an apostrophe. Is there a way to data validate
the input?

Thanks

 

[Bernie Deitrick]

snax,

I am using a macro where the data has to be
excactly the same with an apostrophe.

I think your best bet is to change your macro to accept the number without the quote.

The problem is that even if you enter '876 into cell A1
=LEFT(A1,1)
will return 8 and not ' so it is impossible to use worksheet functions to 'see' the single quote.


HTH,
Bernie
MS Excel MVP

 

[snax500]

I am using Application.Match in my macro in a large range. I think it
is quicker than using a loop through a range.

 

[Guest]

Try this:
Custom Format

"'"0