Numbers change in cell even when formatted as text


M

michaelberrier

I have a list of cells that I'm searching for the value: !5-10 , and I wantto replace all of them (100+) with the value 5-10, essentially just dropping the "!". When I use Find and Replace to do this en masse, it finds the target value just fine, but it changes it to "41769", even when the cell format is set to text.

Incidentally, if I do it manually and delete the "!" in each cell, it showsup as desired.

Why won't it do this automatically and how can I fix that?

Thanks,
mb
 
Ad

Advertisements

C

Claus Busch

Hi Michael,

Am Tue, 28 Oct 2014 06:45:22 -0700 (PDT) schrieb michaelberrier:
Incidentally, if I do it manually and delete the "!" in each cell, it shows up as desired.

search for "!" and replace it with "'"


Regards
Claus B.
 
M

michaelberrier

Hi Michael,

Am Tue, 28 Oct 2014 06:45:22 -0700 (PDT) schrieb michaelberrier:


search for "!" and replace it with "'"


Regards
Claus B.

Claus,
This originally started when I imported a workbook of survey data with a column representing years of experience. Some of the options were "1-3", 3-5", etc. For every one of those that could be translated into a date (1/3/2014), the spreadsheet automatically did that. Is there a way to stop Excel from doing that so I don't have to make this fix in the first place?
 
C

Claus Busch

Hi Michael,

Am Tue, 28 Oct 2014 08:32:37 -0700 (PDT) schrieb michaelberrier:
This originally started when I imported a workbook of survey data with a column representing years of experience. Some of the options were "1-3", 3-5", etc. For every one of those that could be translated into a date (1/3/2014), the spreadsheet automatically did that. Is there a way to stop Excel from doing that so I don't have to make this fix in the first place?

if you format all cells before deleting the "!" the value should remain.
The "'" is only a prefix for text formatted cells. You only see it in
the edit bar. If you see it in the cell you have a leading space.
Then search " !" and replace with "'"

Regards
Claus B.
 
Ad

Advertisements

C

Claus Busch

Hi again,

Am Tue, 28 Oct 2014 08:32:37 -0700 (PDT) schrieb michaelberrier:
This originally started when I imported a workbook of survey data with a column representing years of experience. Some of the options were "1-3", 3-5", etc. For every one of those that could be translated into a date (1/3/2014), the spreadsheet automatically did that. Is there a way to stop Excel from doing that so I don't have to make this fix in the first place?

format the expected column as text before importing the data.


Regards
Claus B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top