Numbers change in cell even when formatted as text

[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

 

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

 

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

 

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

 

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