how would I do this?
 

I have a web query that produces the following:

cell F7 = OVER 8 +115
cell F9 = UNDER 8 -125
cell F11= OVER 9.5 +112
cell F13= UNDER 9.5 -122
cell F15= OVER 8.5 +100
cell F17= UNDER 8.5 -110
cell F19= OVER 10 +106
cell F21= UNDER 10 -116

I would like cells J7:J21 to equal
8
8
9.5
9.5
8.5
8.5
10
10

How would I do this? Thanks


I would like to setup cells J7:J21 so they are equal to

Since the text in each cell has a single blank between the "words", you can
use:

Select the cells. Pulldown Data Text to Columns...
use the space as a delimiter and split the cell up into three parts. Keep
the middle part.
--
Gary's Student


"Jambruins" wrote:

I have a web query that produces the following:

cell F7 = OVER 8 +115
cell F9 = UNDER 8 -125
cell F11= OVER 9.5 +112
cell F13= UNDER 9.5 -122
cell F15= OVER 8.5 +100
cell F17= UNDER 8.5 -110
cell F19= OVER 10 +106
cell F21= UNDER 10 -116

I would like cells J7:J21 to equal
8
8
9.5
9.5
8.5
8.5
10
10

How would I do this? Thanks


I would like to setup cells J7:J21 so they are equal to

To me the easiest is to this via Word.
Copy the contents of the cells to Word.
In Word:
Go to Edit, Replace and (with Wildcards enabled) replace ([0-9.]{1,}) [+-]
with \1 and set the font in the Replace With field to Bold.
Then change paragraph marks to Bold.
Remove everything that is not Bold.
Copy what remains to J17:J21

"Jambruins" wrote:

I have a web query that produces the following:

cell F7 = OVER 8 +115
cell F9 = UNDER 8 -125
cell F11= OVER 9.5 +112
cell F13= UNDER 9.5 -122
cell F15= OVER 8.5 +100
cell F17= UNDER 8.5 -110
cell F19= OVER 10 +106
cell F21= UNDER 10 -116

I would like cells J7:J21 to equal
8
8
9.5
9.5
8.5
8.5
10
10

How would I do this? Thanks


I would like to setup cells J7:J21 so they are equal to

Is there any way to have it done automatically? Something like the right or
left function? Thanks

"Martin P" wrote:

To me the easiest is to this via Word.
Copy the contents of the cells to Word.
In Word:
Go to Edit, Replace and (with Wildcards enabled) replace ([0-9.]{1,}) [+-]
with \1 and set the font in the Replace With field to Bold.
Then change paragraph marks to Bold.
Remove everything that is not Bold.
Copy what remains to J17:J21

"Jambruins" wrote:

I have a web query that produces the following:

cell F7 = OVER 8 +115
cell F9 = UNDER 8 -125
cell F11= OVER 9.5 +112
cell F13= UNDER 9.5 -122
cell F15= OVER 8.5 +100
cell F17= UNDER 8.5 -110
cell F19= OVER 10 +106
cell F21= UNDER 10 -116

I would like cells J7:J21 to equal
8
8
9.5
9.5
8.5
8.5
10
10

How would I do this? Thanks


I would like to setup cells J7:J21 so they are equal to

?B?SmFtYnJ1aW5z?= wrote in
:

I have a web query that produces the following:

cell F7 = OVER 8 +115
cell F9 = UNDER 8 -125
cell F11= OVER 9.5 +112
cell F13= UNDER 9.5 -122
cell F15= OVER 8.5 +100
cell F17= UNDER 8.5 -110
cell F19= OVER 10 +106
cell F21= UNDER 10 -116

I would like cells J7:J21 to equal
8
8
9.5
9.5
8.5
8.5
10
10


As numbers?

In J7:

=VALUE(MID(F7;SEARCH(" ";F7);SEARCH(" ";F7;SEARCH(" ";F7)+1)-SEARCH("
";F7)))

.. . . and copy down.

As text? Then leave VALUE() out.


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

Dodo,
When I paste that in J7 it says the formula you typed contains an error
and highlights the F7 after MID(. Any idea? Thanks

"Dodo" wrote:

?B?SmFtYnJ1aW5z?= wrote in
:

I have a web query that produces the following:

cell F7 = OVER 8 +115
cell F9 = UNDER 8 -125
cell F11= OVER 9.5 +112
cell F13= UNDER 9.5 -122
cell F15= OVER 8.5 +100
cell F17= UNDER 8.5 -110
cell F19= OVER 10 +106
cell F21= UNDER 10 -116

I would like cells J7:J21 to equal
8
8
9.5
9.5
8.5
8.5
10
10


As numbers?

In J7:

=VALUE(MID(F7;SEARCH(" ";F7);SEARCH(" ";F7;SEARCH(" ";F7)+1)-SEARCH("
";F7)))

.. . . and copy down.

As text? Then leave VALUE() out.


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

I realized the formula you gave me contained ; instead of ,. I change all
the ; to , but now it gives me #VALUE!. Any ideas? Thanks

"Dodo" wrote:

?B?SmFtYnJ1aW5z?= wrote in
:

I have a web query that produces the following:

cell F7 = OVER 8 +115
cell F9 = UNDER 8 -125
cell F11= OVER 9.5 +112
cell F13= UNDER 9.5 -122
cell F15= OVER 8.5 +100
cell F17= UNDER 8.5 -110
cell F19= OVER 10 +106
cell F21= UNDER 10 -116

I would like cells J7:J21 to equal
8
8
9.5
9.5
8.5
8.5
10
10


As numbers?

In J7:

=VALUE(MID(F7;SEARCH(" ";F7);SEARCH(" ";F7;SEARCH(" ";F7)+1)-SEARCH("
";F7)))

.. . . and copy down.

As text? Then leave VALUE() out.


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

?B?SmFtYnJ1aW5z?= wrote in
:

I realized the formula you gave me contained ; instead of ,. I change
all the ; to , but now it gives me #VALUE!. Any ideas? Thanks


Sorry, forgot to change my ";" NL separator to your local ",".

Does the #VALUE! happen in all rows?

I get the same error when I e.g. change "OVER 9,5 +112" to "OVER 9.5 +112"
because then the result between value() is not a valid number format here
(the reverse for you).

Did you try to use:

=MID(F7,SEARCH(" ",F7),SEARCH(" ",F7,SEARCH(" ",F7)+1)-SEARCH(" ",F7))

What do you get then?


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

I get OVER. There is a space before the word over or under. Does that
matter? thanks

"Dodo" wrote:

?B?SmFtYnJ1aW5z?= wrote in
:

I realized the formula you gave me contained ; instead of ,. I change
all the ; to , but now it gives me #VALUE!. Any ideas? Thanks


Sorry, forgot to change my ";" NL separator to your local ",".

Does the #VALUE! happen in all rows?

I get the same error when I e.g. change "OVER 9,5 +112" to "OVER 9.5 +112"
because then the result between value() is not a valid number format here
(the reverse for you).

Did you try to use:

=MID(F7,SEARCH(" ",F7),SEARCH(" ",F7,SEARCH(" ",F7)+1)-SEARCH(" ",F7))

What do you get then?


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

In cell G7:
=FIND("ER",F7)
In cell H7:
=MID(F7,G7+3,3)
In cell I7:
=SUBSTITUTE(H7,"+","")
In cell J7:
=SUBSTITUTE(I7,"-","")
Copy G7 to J7 down.

"Jambruins" wrote:

Is there any way to have it done automatically? Something like the right or
left function? Thanks

"Martin P" wrote:

To me the easiest is to this via Word.
Copy the contents of the cells to Word.
In Word:
Go to Edit, Replace and (with Wildcards enabled) replace ([0-9.]{1,}) [+-]
with \1 and set the font in the Replace With field to Bold.
Then change paragraph marks to Bold.
Remove everything that is not Bold.
Copy what remains to J17:J21

"Jambruins" wrote:

I have a web query that produces the following:

cell F7 = OVER 8 +115
cell F9 = UNDER 8 -125
cell F11= OVER 9.5 +112
cell F13= UNDER 9.5 -122
cell F15= OVER 8.5 +100
cell F17= UNDER 8.5 -110
cell F19= OVER 10 +106
cell F21= UNDER 10 -116

I would like cells J7:J21 to equal
8
8
9.5
9.5
8.5
8.5
10
10

How would I do this? Thanks


I would like to setup cells J7:J21 so they are equal to

?B?SmFtYnJ1aW5z?= wrote in
:

I get OVER. There is a space before the word over or under. Does
that matter? thanks


Okay, that makes a difference. The first space got lost somewhere on the
way in this newsgroup. ;-)))

Is it just 1 space? Then we have to start looking for spaces at position 2.
Just change the formula to:

=VALUE(MID(F7,SEARCH(" ",F7,2),SEARCH(" ",F7,SEARCH(" ",F7,2)+1)-SEARCH("
",F7,2)))

That should do the trick!


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

thanks, that works great

"Dodo" wrote:

?B?SmFtYnJ1aW5z?= wrote in
:

I get OVER. There is a space before the word over or under. Does
that matter? thanks


Okay, that makes a difference. The first space got lost somewhere on the
way in this newsgroup. ;-)))

Is it just 1 space? Then we have to start looking for spaces at position 2.
Just change the formula to:

=VALUE(MID(F7,SEARCH(" ",F7,2),SEARCH(" ",F7,SEARCH(" ",F7,2)+1)-SEARCH("
",F7,2)))

That should do the trick!


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

?B?SmFtYnJ1aW5z?= wrote in
:

thanks, that works great


Glad to be of help!


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)