I have a web query which gives me the result "385/1700" and all I want is the
first part "385" to allow me to do calculations with, but because it's a web
query, it wont let me just format it as a fraction and times it by 1700.. it
comes up with the #VALUE! error.

So I dont know what else to try,
Can anyone help?

Thanks,
Aden

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Aden" wrote in message
...
I have a web query which gives me the result "385/1700" and all I want is
the
first part "385" to allow me to do calculations with, but because it's a
web
query, it wont let me just format it as a fraction and times it by 1700..
it
comes up with the #VALUE! error.

So I dont know what else to try,
Can anyone help?

Thanks,
Aden

I would use DataText to Columns with / as the delimiter and skip the righthand
column then Finish.


Gord Dibben MS Excel MVP

On Sat, 10 May 2008 15:21:00 -0700, Aden wrote:

I have a web query which gives me the result "385/1700" and all I want is the
first part "385" to allow me to do calculations with, but because it's a web
query, it wont let me just format it as a fraction and times it by 1700.. it
comes up with the #VALUE! error.

So I dont know what else to try,
Can anyone help?

Thanks,
Aden

There is no excuse for this other than it's a weekend and I'm bored<g, but
here is an alternative formula to do the same thing...

=INT(--SUBSTITUTE(A17,"/","."))

Rick


"Bob Phillips" wrote in message
...
=--(LEFT(A1,FIND("/",A1)-1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Aden" wrote in message
...
I have a web query which gives me the result "385/1700" and all I want is
the
first part "385" to allow me to do calculations with, but because it's a
web
query, it wont let me just format it as a fraction and times it by 1700..
it
comes up with the #VALUE! error.

So I dont know what else to try,
Can anyone help?

Thanks,
Aden

Hello,

Or
=--(LEFT(A1,FIND("/",A1&"/")-1))
with a little insurance against non-appearing "/"'s.

Regards,
Bernd

LOL...

Now, for the same functionality, my formula ends up being the shorter
one.<g

Well, okay, it is not exactly the same functionality... yours will survive
an entry like 123/abc where as mine won't, but the OP did ask how to
separate "numbers", so that shouldn't be a problem.

Rick


"Bernd P" wrote in message
...
Hello,

Or
=--(LEFT(A1,FIND("/",A1&"/")-1))
with a little insurance against non-appearing "/"'s.

Regards,
Bernd

That makes good sense to me <G

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Bernd P" wrote in message
...
Hello,

Or
=--(LEFT(A1,FIND("/",A1&"/")-1))
with a little insurance against non-appearing "/"'s.

Regards,
Bernd

Hello,

ok ok, but just for the fun of it:
=regexpreplace(A1,"^(\d+).*$","$1")

The UDF is he
http://www.sulprobil.com/html/regexp.html

Regards,
Bernd

On Sun, 11 May 2008 09:31:09 -0700 (PDT), Bernd P wrote:

Hello,

ok ok, but just for the fun of it:
=regexpreplace(A1,"^(\d+).*$","$1")

The UDF is he
http://www.sulprobil.com/html/regexp.html

Regards,
Bernd

OR,if you have Longre's morefunc.xll installed (see
http://xcell05.free.fr/morefunc/english/index.htm )

=--REGEX.MID(A1,"\d+")



--ron

That returns 1 for an entry of 1a2/3 (I'm guessing it is quitting at the
first non-digit)... I would think it should return the original text if the
text doesn't meet the pattern number/slash/number. Here is my non-RegEx
attempt for a UDF...

Function GetNumberBeforeSlash(Source As Variant) As Variant
If Not Left(Source, InStr(Source & "/", "/") - 1) Like "*[!0-9]*" And _
Source Like "?*/*" And Not Source Like "*/*/*" Then
GetNumberBeforeSlash = Left(Source, InStr(Source, "/") - 1)
Else
GetNumberBeforeSlash = Source
End If
End Function

Rick


"Bernd P" wrote in message
...
Hello,

ok ok, but just for the fun of it:
=regexpreplace(A1,"^(\d+).*$","$1")

The UDF is he
http://www.sulprobil.com/html/regexp.html

Regards,
Bernd

On Sun, 11 May 2008 14:45:45 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

I would think it should return the original text if the
text doesn't meet the pattern number/slash/number.

Then you just change the regex a bit:

=REGEX.SUBSTITUTE(A1,"^(\d+)/\d+$","[1]")

OR, if you want it to return nothing, which would be my preference:

=REGEX.MID(A1,"^\d+(?=/\d+$)")



--ron

No... many years ago I worked with regular expressions in the Unix world and
am well aware of their many "charms".<g

Rick


"Rob L" wrote in message
u...
I'll bet he's sorry he asked THAT question now.....

Rob L

"Ron Rosenfeld" wrote in message
...
On Sun, 11 May 2008 14:45:45 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

I would think it should return the original text if the
text doesn't meet the pattern number/slash/number.

Then you just change the regex a bit:

=REGEX.SUBSTITUTE(A1,"^(\d+)/\d+$","[1]")

OR, if you want it to return nothing, which would be my preference:

=REGEX.MID(A1,"^\d+(?=/\d+$)")



--ron

I'll bet he's sorry he asked THAT question now.....

Rob L

"Ron Rosenfeld" wrote in message
...
On Sun, 11 May 2008 14:45:45 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

I would think it should return the original text if the
text doesn't meet the pattern number/slash/number.

Then you just change the regex a bit:

=REGEX.SUBSTITUTE(A1,"^(\d+)/\d+$","[1]")

OR, if you want it to return nothing, which would be my preference:

=REGEX.MID(A1,"^\d+(?=/\d+$)")



--ron

Thanks Bob! :D Exactly the formula I needed...
Im shocked people actually know this! It seems really complicated...
I suppose the more I use it the better I'll become... Anyway, I'm only 14 so
I have a reason :D

Do you know of any good sites which help with Excel? because the one I am
working on at the moment is quite complex and I don't know a lot of the
formulas.

Thanks,
Aden

"Bob Phillips" wrote:

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Aden" wrote in message
...
I have a web query which gives me the result "385/1700" and all I want is
the
first part "385" to allow me to do calculations with, but because it's a
web
query, it wont let me just format it as a fraction and times it by 1700..
it
comes up with the #VALUE! error.

So I dont know what else to try,
Can anyone help?

Thanks,
Aden

Why don't you post it here?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Aden" wrote in message
...
Thanks Bob! :D Exactly the formula I needed...
Im shocked people actually know this! It seems really complicated...
I suppose the more I use it the better I'll become... Anyway, I'm only 14
so
I have a reason :D

Do you know of any good sites which help with Excel? because the one I am
working on at the moment is quite complex and I don't know a lot of the
formulas.

Thanks,
Aden

"Bob Phillips" wrote:

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Aden" wrote in message
...
I have a web query which gives me the result "385/1700" and all I want
is
the
first part "385" to allow me to do calculations with, but because it's
a
web
query, it wont let me just format it as a fraction and times it by
1700..
it
comes up with the #VALUE! error.

So I dont know what else to try,
Can anyone help?

Thanks,
Aden