how would I do this?

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

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

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

 

[Dodo]

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.

 

[Guest]

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

 

[Guest]

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]

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?

 

[Guest]

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

 

[Guest]

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.

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

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

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

 

[Dodo]

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!

 

[Guest]

thanks, that works great

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!

 

[Dodo]

thanks, that works great

Glad to be of help!