How do I separate numbers?

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

Bob
Sat May 10 18:53:19 CDT 2008

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

--
HTH

Bob

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

"Aden" <Aden@discussions.microsoft.com> wrote in message
news:794931F9-B9D2-4DE8-8AD1-F7BBAD9F4F8D@microsoft.com...
>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

Gord
Sat May 10 19:31:04 CDT 2008

I would use Data>Text 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 <Aden@discussions.microsoft.com> 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

Rick
Sat May 10 22:30:59 CDT 2008

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" <bob.ngs@somewhere.com> wrote in message
news:OCgWEkvsIHA.4076@TK2MSFTNGP06.phx.gbl...
> =--(LEFT(A1,FIND("/",A1)-1))
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "Aden" <Aden@discussions.microsoft.com> wrote in message
> news:794931F9-B9D2-4DE8-8AD1-F7BBAD9F4F8D@microsoft.com...
>>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
>
>

Bernd
Sun May 11 01:52:48 CDT 2008

Hello,

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

Regards,
Bernd

Rick
Sun May 11 03:29:37 CDT 2008

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" <bplumhoff@gmail.com> wrote in message
news:db11fc7d-e597-4a33-8c9d-dcbaf8cc374b@f36g2000hsa.googlegroups.com...
> Hello,
>
> Or
> =--(LEFT(A1,FIND("/",A1&"/")-1))
> with a little insurance against non-appearing "/"'s.
>
> Regards,
> Bernd

Bob
Sun May 11 05:05:27 CDT 2008

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" <bplumhoff@gmail.com> wrote in message
news:db11fc7d-e597-4a33-8c9d-dcbaf8cc374b@f36g2000hsa.googlegroups.com...
> Hello,
>
> Or
> =--(LEFT(A1,FIND("/",A1&"/")-1))
> with a little insurance against non-appearing "/"'s.
>
> Regards,
> Bernd

Bernd
Sun May 11 11:31:09 CDT 2008

Hello,

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

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

Regards,
Bernd

Ron
Sun May 11 12:39:02 CDT 2008

On Sun, 11 May 2008 09:31:09 -0700 (PDT), Bernd P <bplumhoff@gmail.com> wrote:

>Hello,
>
>ok ok, but just for the fun of it:
>=regexpreplace(A1,"^(\d+).*$","$1")
>
>The UDF is here:
>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

Rick
Sun May 11 13:45:45 CDT 2008

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" <bplumhoff@gmail.com> wrote in message
news:745bcc49-2770-4a54-a308-87b69b98f163@y21g2000hsf.googlegroups.com...
> Hello,
>
> ok ok, but just for the fun of it:
> =regexpreplace(A1,"^(\d+).*$","$1")
>
> The UDF is here:
> http://www.sulprobil.com/html/regexp.html
>
> Regards,
> Bernd

Ron
Sun May 11 16:05:22 CDT 2008

On Sun, 11 May 2008 14:45:45 -0400, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.SPAM@NO.SPAMverizon.net> 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

Rob
Mon May 12 20:54:09 CDT 2008

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

Rob L

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:qlne245giuhsd7mon1r3u4begg6a9mjanl@4ax.com...
> On Sun, 11 May 2008 14:45:45 -0400, "Rick Rothstein \(MVP - VB\)"
> <rick.newsNO.SPAM@NO.SPAMverizon.net> 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

Rick
Mon May 12 04:40:40 CDT 2008

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" <robertleppertake@thisoptusnet.outcom.au> wrote in message
news:482805d3$0$17505$afc38c87@news.optusnet.com.au...
> I'll bet he's sorry he asked THAT question now.....
>
> Rob L
>
> "Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
> news:qlne245giuhsd7mon1r3u4begg6a9mjanl@4ax.com...
>> On Sun, 11 May 2008 14:45:45 -0400, "Rick Rothstein \(MVP - VB\)"
>> <rick.newsNO.SPAM@NO.SPAMverizon.net> 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
>
>

Aden
Fri May 16 12:47:01 CDT 2008

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" <Aden@discussions.microsoft.com> wrote in message
> news:794931F9-B9D2-4DE8-8AD1-F7BBAD9F4F8D@microsoft.com...
> >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
>
>
>

Bob
Fri May 16 18:30:40 CDT 2008

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" <Aden@discussions.microsoft.com> wrote in message
news:3586F87B-67D9-4BC8-A0D0-076ED8F51E39@microsoft.com...
> 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" <Aden@discussions.microsoft.com> wrote in message
>> news:794931F9-B9D2-4DE8-8AD1-F7BBAD9F4F8D@microsoft.com...
>> >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
>>
>>
>>