C
CompleteNewb
Okay, I want you to know I usually try to do my best in terms of searching
for solutions and experimenting before posting, but this one is eluding all
my attempts.
Using Excel 2000, but same thing happens in 2003:
I have a column of cells with text in it, all of them like this:
10 to 12
11 to 15
I wanted to replace the " to " (space, the word "to, and another space) with
a dash, leaving this:
10-12
11-15
I tried a find/replace, which Excel assumed was wanting dates. Thus, when I
did the replace, Excel would show:
10-December
in the cell and
12/10/2007
in the formula bar.
I figured this was Excel making the assumption that "10-11" must be a date,
and so I formatted the resulting cell as text, thinking that Excel would say
to itself "Oh, okay, I made the wrong assumption, so the cell should show
"10-11." Instead, it changed the value to:
39213
I have no idea what 39213 is. I assume it's some kind of julian or
something equivalent to date values.
But anyway, knowing that putting a single quote in front of values placed in
a cell makes Excel say "Okay, this is text, so I need to display everything
after the single quote exactly as it's entered," I tried using this formula
in a cell next to the original column (let's say Original Column is A):
= "'" & A1
This put the following in the cell:
'10 to 12
Now, hand-typing the single quote and a value would not show the single
quote; it would show just:
10 to 12
So, apparently using a formula to use the whole single quote technique
doesn't work. Excel sees that as "this single quote is not just an
indication that the stuff AFTER the single quote is text; the user actually
wants to SEE the single quote." This I was NOT expecting. (one little side
question here is, how can one use a formula to have the single quote be the
same as the one you'd hand-type in, which DOESN'T show up in the cell once
you hit Enter or Tab or whatever).
Now, eventually I solved this by doing a "text to columns", making three
columns (one with "10", one with "to", and one with "12", and THEN using a
formula in a 4th column to concatenate columns 1 and 3 and inserting the
dash in between (=A1 & "-" & C1). However, why would I have to do all that?
Is there no way to replace the word "to" with a dash and tell Excel NOT to
assume all kinds of crazy things about the resulting format? Nothing I
tried in terms of tools-->options (auto-format as I type, auto-correct,
etc.) would STOP Excel from doing this weird assumption of my find/replace
results.
Any help, advice, etc. on this is appreciated. And thanks for reading, you
guys are great.
for solutions and experimenting before posting, but this one is eluding all
my attempts.
Using Excel 2000, but same thing happens in 2003:
I have a column of cells with text in it, all of them like this:
10 to 12
11 to 15
I wanted to replace the " to " (space, the word "to, and another space) with
a dash, leaving this:
10-12
11-15
I tried a find/replace, which Excel assumed was wanting dates. Thus, when I
did the replace, Excel would show:
10-December
in the cell and
12/10/2007
in the formula bar.
I figured this was Excel making the assumption that "10-11" must be a date,
and so I formatted the resulting cell as text, thinking that Excel would say
to itself "Oh, okay, I made the wrong assumption, so the cell should show
"10-11." Instead, it changed the value to:
39213
I have no idea what 39213 is. I assume it's some kind of julian or
something equivalent to date values.
But anyway, knowing that putting a single quote in front of values placed in
a cell makes Excel say "Okay, this is text, so I need to display everything
after the single quote exactly as it's entered," I tried using this formula
in a cell next to the original column (let's say Original Column is A):
= "'" & A1
This put the following in the cell:
'10 to 12
Now, hand-typing the single quote and a value would not show the single
quote; it would show just:
10 to 12
So, apparently using a formula to use the whole single quote technique
doesn't work. Excel sees that as "this single quote is not just an
indication that the stuff AFTER the single quote is text; the user actually
wants to SEE the single quote." This I was NOT expecting. (one little side
question here is, how can one use a formula to have the single quote be the
same as the one you'd hand-type in, which DOESN'T show up in the cell once
you hit Enter or Tab or whatever).
Now, eventually I solved this by doing a "text to columns", making three
columns (one with "10", one with "to", and one with "12", and THEN using a
formula in a 4th column to concatenate columns 1 and 3 and inserting the
dash in between (=A1 & "-" & C1). However, why would I have to do all that?
Is there no way to replace the word "to" with a dash and tell Excel NOT to
assume all kinds of crazy things about the resulting format? Nothing I
tried in terms of tools-->options (auto-format as I type, auto-correct,
etc.) would STOP Excel from doing this weird assumption of my find/replace
results.
Any help, advice, etc. on this is appreciated. And thanks for reading, you
guys are great.