How do I separate numbers?

[Aden]

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 Phillips]

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

--
HTH

Bob

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

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bernd P]

Hello,

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

Regards,
Bernd

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bob Phillips]

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]

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 Rosenfeld]

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 Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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 Rothstein \(MVP - VB\)]

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

Rick

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

Rob L

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 L]

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

Rob L

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]

Thanks Bob! 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

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]

Why don't you post it here?

--
HTH

Bob

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