extracting numbers from a text field and applying formula

[alex10for2]

here's my question...
i have a cell that has a text field of this type: '9-19' (a-b)
I want to create a formula that will subtract 19-9 (b-a)
and return the result as a number//

can anyone help?! is this even doable? I'd really appreciate it!

PS
also, i'm very fairly new at excel so a detailed response with th
formula would really help!
ALe

 

[Immanuel]

Try:

=MID(A1,FIND("-",A1)+1,99)-LEFT(A1,FIND("-",A1)-1)

Won't attempt an explanation just yet. If you have specific questions,
please post!

/i.

 

[alex10for2]

http://cait.cpmc.columbia.edu:88/dept/sph/internal/stats.xls

There's the file. It's showing an error. It really is for recreationa
purposes (although I'm eager to learn now that I see that excel isn'
as simple as I once thought). If you could give it a shot it woul
probably help a lot if you have the file.

In any case I'm trying to get the difference between field goals take
and field goals made. The problem is that I would be getting the dat
in text format ('9-19') so that might be what's giving the error..

Thank you!!! I really appreciate your expertise. If you can help me ou
and get it to work that would be great. My email is (e-mail address removed)

THanks again!

Ale

 

[Anders S]

Alex,

Immanuel's formula works OK. The problem is that there are invisible characters in Column G. Double-click a cell and use the left/right arrow keys and you'll see.

Get rid of the junk characters and you'll be OK.

Regards
Anders Silvén

 

[Ron Rosenfeld]

here's my question...
i have a cell that has a text field of this type: '9-19' (a-b)
I want to create a formula that will subtract 19-9 (b-a)
and return the result as a number//

can anyone help?! is this even doable? I'd really appreciate it!

PS
also, i'm very fairly new at excel so a detailed response with the
formula would really help!
ALex

One way is to download and install morefunc.xll from

http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm#Morefunc.xll.



If 9-19 is as text in A1, then:

=-EVAL(A1)

will give you the result you want.

Note that if you enter 9-19 and it is NOT text, Excel will interpret it as a
date (9/19/2003 if you enter it today).

This EVAL function Evaluates a formula or expression that is in the form of
text and returns the result.


--ron

 

[immanuel]

The junk characters are specifically non-breaking spaces (character 160 in
the ascii table). So you can revise the original formula like so:

=MID(CLEAN(SUBSTITUTE(G4,CHAR(160),"")),FIND("-",CLEAN(SUBSTITUTE(G4,CHAR(16
0),"")))+1,99)-LEFT(CLEAN(SUBSTITUTE(G4,CHAR(160),"")),FIND("-",CLEAN(SUBSTI
TUTE(G4,CHAR(160),"")))-1)

/i.

Alex,

Immanuel's formula works OK. The problem is that there are invisible
characters in Column G. Double-click a cell and use the left/right arrow
keys and you'll see.

Get rid of the junk characters and you'll be OK.

Regards
Anders Silvén

 

[alex10for2]

thank you ! that worked great! one last question..... the text strin
i'm getting has spaces in it so the function isn't workin
properly...is there a function to remove spaces from a cell?

thanks!

 

[Peo Sjoblom]

=substitute(a1," ","")

 

[alex10for2]

i can't get it to work, it doesn't look like it's deleting the spaces
anyone know of a function that does this?

thanks!

ALe

 

[Peo Sjoblom]

Did you copy this from the web? If so try this amendment

=CLEAN(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),"")," ",""))

 

[alex10for2]

wow! thanx! you were right on the money..it was from the web, and NOW it
works!

Thank you. I really appreciate all of your help... I'm sure I'll be
back with more questions, but i think i may come directly to you if
thats ok......very impressive!

thnx again

Alex,