Need help with pulling out data between brackets
 

Hi folks,

I'm pulling down data off a web query from a trivia game site. Data is
listed for 20 + games, each in a row. In one column (c), it will show where
I'm averaging in points earned via 10999 (212th) and in another column (g),
same row, show how many players were playing 15537.

I'd like to set up another column showing what percentile I stand at, by
pulling out the numbers within the ( ), and removing the th or nd or st.
IOW, set up a formula 212/15537 or whatever the updated shows.

Any easy way to pull out the data within the brackets?

TIA,

Bob

Need help with pulling out data between brackets
 

Is this all in one cell: 10999 (212th)

C1 = 10999 (212th)
G1 = 15537

=IF(ISERROR(FIND("(",C1)),0,--SUBSTITUTE(MID(C1,FIND("(",C1)+1,255),RIGHT(C1,3), ""))/G1

Format as %

Result = 1.36%

Biff

"Bob Smith" wrote in message
nk.net...
Hi folks,

I'm pulling down data off a web query from a trivia game site. Data is
listed for 20 + games, each in a row. In one column (c), it will show
where I'm averaging in points earned via 10999 (212th) and in another
column (g), same row, show how many players were playing 15537.

I'd like to set up another column showing what percentile I stand at, by
pulling out the numbers within the ( ), and removing the th or nd or st.
IOW, set up a formula 212/15537 or whatever the updated shows.

Any easy way to pull out the data within the brackets?

TIA,

Bob

Need help with pulling out data between brackets
 

The easies way is to use in the Edit Menu - Replace
Put in the From box the character you want to remove like [ and in the to
box put nothing. You can highlight all the cells that you want character
removed. But you have to remove character in groups as the occur in each
cell. Putting [] will only remove these characters if the are next to each
other.

"Bob Smith" wrote:

Hi folks,

I'm pulling down data off a web query from a trivia game site. Data is
listed for 20 + games, each in a row. In one column (c), it will show where
I'm averaging in points earned via 10999 (212th) and in another column (g),
same row, show how many players were playing 15537.

I'd like to set up another column showing what percentile I stand at, by
pulling out the numbers within the ( ), and removing the th or nd or st.
IOW, set up a formula 212/15537 or whatever the updated shows.

Any easy way to pull out the data within the brackets?

TIA,

Bob

Need help with pulling out data between brackets
 

Biff, you are my hero! ... Well at least a good online acquaintance ... :).
That formula works great. TYVM

Bob

"T. Valko" wrote in message
...
Is this all in one cell: 10999 (212th)

C1 = 10999 (212th)
G1 = 15537

=IF(ISERROR(FIND("(",C1)),0,--SUBSTITUTE(MID(C1,FIND("(",C1)+1,255),RIGHT(C1,3), ""))/G1

Format as %

Result = 1.36%

Biff

"Bob Smith" wrote in message
nk.net...
Hi folks,

I'm pulling down data off a web query from a trivia game site. Data is
listed for 20 + games, each in a row. In one column (c), it will show
where I'm averaging in points earned via 10999 (212th) and in another
column (g), same row, show how many players were playing 15537.

I'd like to set up another column showing what percentile I stand at, by
pulling out the numbers within the ( ), and removing the th or nd or st.
IOW, set up a formula 212/15537 or whatever the updated shows.

Any easy way to pull out the data within the brackets?

TIA,

Bob

Need help with pulling out data between brackets
 

Since an arithmetic operation is taking place you can leave out the "--":

=IF(ISERROR(FIND("(",C1)),0,SUBSTITUTE(MID(C1,FIND ("(",C1)+1,255),RIGHT(C1,3),""))/G1

The result of the Substitute function is a TEXT number.

The "--" coerces a TEXT number into a NUMERIC number. An arithmetic
operation will do the same thing.

Biff

"T. Valko" wrote in message
...
Is this all in one cell: 10999 (212th)

C1 = 10999 (212th)
G1 = 15537

=IF(ISERROR(FIND("(",C1)),0,--SUBSTITUTE(MID(C1,FIND("(",C1)+1,255),RIGHT(C1,3), ""))/G1

Format as %

Result = 1.36%

Biff

"Bob Smith" wrote in message
nk.net...
Hi folks,

I'm pulling down data off a web query from a trivia game site. Data is
listed for 20 + games, each in a row. In one column (c), it will show
where I'm averaging in points earned via 10999 (212th) and in another
column (g), same row, show how many players were playing 15537.

I'd like to set up another column showing what percentile I stand at, by
pulling out the numbers within the ( ), and removing the th or nd or st.
IOW, set up a formula 212/15537 or whatever the updated shows.

Any easy way to pull out the data within the brackets?

TIA,

Bob

Need help with pulling out data between brackets
 

You're welcome. Thanks for the feedback!

Biff

"Bob Smith" wrote in message
nk.net...
Biff, you are my hero! ... Well at least a good online acquaintance ...
:). That formula works great. TYVM

Bob

"T. Valko" wrote in message
...
Is this all in one cell: 10999 (212th)

C1 = 10999 (212th)
G1 = 15537

=IF(ISERROR(FIND("(",C1)),0,--SUBSTITUTE(MID(C1,FIND("(",C1)+1,255),RIGHT(C1,3), ""))/G1

Format as %

Result = 1.36%

Biff

"Bob Smith" wrote in message
nk.net...
Hi folks,

I'm pulling down data off a web query from a trivia game site. Data is
listed for 20 + games, each in a row. In one column (c), it will show
where I'm averaging in points earned via 10999 (212th) and in another
column (g), same row, show how many players were playing 15537.

I'd like to set up another column showing what percentile I stand at, by
pulling out the numbers within the ( ), and removing the th or nd or st.
IOW, set up a formula 212/15537 or whatever the updated shows.

Any easy way to pull out the data within the brackets?

TIA,

Bob

Need help with pulling out data between brackets
 

=--MID(C1,FIND("(",C1)+1,LEN(C1)-FIND("(",C1)-3)/G1


"Bob Smith" wrote:

Hi folks,

I'm pulling down data off a web query from a trivia game site. Data is
listed for 20 + games, each in a row. In one column (c), it will show where
I'm averaging in points earned via 10999 (212th) and in another column (g),
same row, show how many players were playing 15537.

I'd like to set up another column showing what percentile I stand at, by
pulling out the numbers within the ( ), and removing the th or nd or st.
IOW, set up a formula 212/15537 or whatever the updated shows.

Any easy way to pull out the data within the brackets?

TIA,

Bob