Always have apostrophe
 

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

Always have apostrophe
 

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" wrote in message
oups.com...
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

Always have apostrophe
 

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


On Jun 22, 10:56 am, "Earl Kiosterud" wrote:
Snax,

First of all, if you want theapostropheto 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 theapostropheis 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 anapostrophe(s) automatically inserted is with a macro. We can
write you one if you're interested.
--
Earl Kiosterudwww.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" wrote in message

oups.com...



In Excel2000, is there a way to force anapostrophein 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 anapostropheadded to anything input.

Thanks- Hide quoted text -

- Show quoted text -

Always have apostrophe
 

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" wrote in message
oups.com...
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


On Jun 22, 10:56 am, "Earl Kiosterud" wrote:
Snax,

First of all, if you want theapostropheto 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 theapostropheis 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 anapostrophe(s) automatically inserted is with a macro. We can
write you one if you're interested.
--
Earl Kiosterudwww.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"
wrote in message

oups.com...



In Excel2000, is there a way to force anapostrophein 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 anapostropheadded to anything input.

Thanks- Hide quoted text -

- Show quoted text -

Always have apostrophe
 

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



On Jun 22, 11:51 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
snax,

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

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" wrote in message

oups.com...



All that people would be inputting in is numbers. I just want there to
be a leadingapostropheon 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 anapostrophe. Is there a way to data validate
the input?

Thanks

On Jun 22, 10:56 am, "Earl Kiosterud" wrote:
Snax,

First of all, if you want theapostropheto 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 theapostropheis 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 anapostrophe(s) automatically inserted is with a macro. We can
write you one if you're interested.
--
Earl Kiosterudwww.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...
-----------------------------------------------------------------------"sna*x500"
wrote in message

groups.com...

In Excel2000, is there a way to force anapostrophein 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 anapostropheadded to anything input.

Thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Always have apostrophe
 

Try this:
Custom Format

"'"0


"snax500" wrote:

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