Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
?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) |
#6
|
|||
|
|||
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) |
#7
|
|||
|
|||
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) |
#8
|
|||
|
|||
?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) |
#9
|
|||
|
|||
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) |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
?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) |
#12
|
|||
|
|||
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) |
#13
|
|||
|
|||
?B?SmFtYnJ1aW5z?= wrote in
: thanks, that works great Glad to be of help! -- It is I, DeauDeau (Free after monsieur Leclerc in 'Allo, 'allo) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|