Changing value in cell

  • Thread starter Thread starter Allen
  • Start date Start date
A

Allen

Is it possible to change an alphanumeric cell content
into numeric only? I have a table with temperatures
recorded in various years (such as "61°F(1985)") and I
want to change this to "61", by eliminating the "°F
(1985)", so I can use the numeric values in a graph.
 
One way:

Assuming your data are in column A, in an unused column enter

=LEFT(A1,FIND("°",A1)-1)

and copy down as far as necessary.

You can then copy the cells and paste special/values over the original
data if desired.
 
Hi Jim

just as a matter of interest how did you get the ° into the formula?

Cheers
JulieD
 
If the entries are always formatted as you indicate,
create a new column to hold ...
=VALUE(LEFT(A1,FIND("°",A1)-1))

for entries starting A1. This should extract all positive
and negative values, regardless of length. If the entry
format varies from your example, you'll have to modify the
formula to match the situation.

Hope this helps.
 
Hi Andy,
You've touched on an interesting item - I've been
unable to type Alt+ characters into a cell without getting
a "no-go" beep. I've had to resort to copying an existing
character (as did JEMcG) or pulling it out of the
Character Map via copy/paste. Can these be typed directly
somehow?

Thanks.
 
Well, don't worry. It works for me!! I won't insult you by reminding you to
use the keypad - I'm sure you already know that! CHAR(186) gives you º too.

--
Andy.


Hi Andy,
You've touched on an interesting item - I've been
unable to type Alt+ characters into a cell without getting
a "no-go" beep. I've had to resort to copying an existing
character (as did JEMcG) or pulling it out of the
Character Map via copy/paste. Can these be typed directly
somehow?

Thanks.
 
Sorry for my ignorance - you are absolutely correct about
the keypad vice the number keys - it never occurred to
me. Thanks! It will amuse you to know that I also had to
learn about the space bar to check and uncheck T/F boxes.

BTW - in my original reply to Allen, I wrapped my offering
of the Left function expression inside the value
function. I thought it was necessary to do that to get
graphable numbers. Have I missed something else as well?

s/ Old guy trying to learn how to skin Office cats and
keep up with the young guys (and gals, too; hat off to
JulieD, whose toes I stepped on accidentally one day, and
who graciously let me off the hook!).
 
We all have loads to learn - so don't worry about that!!

Sticking the VALUE function in there won't do any harm. It will correct any
errors caused by leading/trailing spaces though.

--
Andy.


Sorry for my ignorance - you are absolutely correct about
the keypad vice the number keys - it never occurred to
me. Thanks! It will amuse you to know that I also had to
learn about the space bar to check and uncheck T/F boxes.

BTW - in my original reply to Allen, I wrapped my offering
of the Left function expression inside the value
function. I thought it was necessary to do that to get
graphable numbers. Have I missed something else as well?

s/ Old guy trying to learn how to skin Office cats and
keep up with the young guys (and gals, too; hat off to
JulieD, whose toes I stepped on accidentally one day, and
who graciously let me off the hook!).
 
JE McGimpsey wrote...
Well, not for me. OPT-SHIFT-8 works, though...
Click to expand...

Ah, the benefits of hardware-specific digressions in newsgroup
catering to Mac and Wintel users
 
hgrove said:
Ah, the benefits of hardware-specific digressions in newsgroups
catering to Mac and Wintel users.
Click to expand...

I generally do that only when explicitly hardware specific answers have
been posted (which is why I posted a generic answer first).

Further OT:

Harlan - JFMI, did you explicitly enter the angle brackets in your email
address at ExcelForum? Your From line has an extra pair:

From: hgrove <<[email protected]>>

It has the interesting side-effect of leaving the "From" field blank for
your posts in my newsreader's article list...

I suspect it's a problem with ExcelForum, since I've seen a couple of
other posts with from that domain with the same problem, but I can't
tell for sure since those posters may have entered brackets too.
 
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk
Andy B said:
Well, don't worry. It works for me!! I won't insult you by reminding you to
use the keypad - I'm sure you already know that! CHAR(186) gives you º too.
Click to expand...

CHAR(186) does look like the degree symbol in 10 point but I believe that it
is actually a superscript letter O

Sandy
 
Back
Top