How do I separate numbers?

A

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
 
B

Bob Phillips

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

--
HTH

Bob

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

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
 
R

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
 
B

Bernd P

Hello,

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

Regards,
Bernd
 
R

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
 
B

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)
 
R

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
 
R

Ron Rosenfeld

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

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
 
R

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


Rob L said:
I'll bet he's sorry he asked THAT question now.....

Rob L

Ron Rosenfeld said:
I would think it should return the original text if the
text doesn't meet the pattern number/slash/number.
Click to expand...

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
Click to expand...
Click to expand...
 
R

Rob L

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

Rob L

Ron Rosenfeld said:
I would think it should return the original text if the
text doesn't meet the pattern number/slash/number.
Click to expand...

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
Click to expand...
 
A

Aden

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
 
B

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)
 
Top