IFERROR & VALUE Help (need to convert numbers from text to value, but when there is a space instead of 7 there is a 07 it doesnt work.)

[Lou Pointspreads]

Hey Folks,

Newbie to the community here, trying to update weekly football scores that i copy and paste from a website. (covers.com)

The football scores come in this format W/L (win or loss) and the score with a hyphen in the middle.

"L 31-33" would be a loss as the team scored 31 points and allowed 33 points against.

So i only need 31 in one cell and 33 in another.

I used a =LEFT((+RIGHT(I3,5)),2) to get the two digits on the left, points for without the L o the space next to it.
I used a +RIGHT(I3,2) to get the two digits on the right.

They return as a string, so i needed to use VALUE to turn them back into numbers.

=VALUE(LEFT((+RIGHT(I3,5)),2))
=VALUE(+RIGHT(I3,2))

so i figured this would work, and it did sort of. Except for when the score was of a single digit, say 0-9 because it includes a preceding space which value is unable to convert back to a number.

The formula works fine if i manually fix the score to "07" instead of 7, but who has time to manually anything these days.

I wanted to use an IFERROR, and did the following with no luck:

=IFERROR(VALUE(LEFT((+RIGHT(I15,5)),2)),VALUE(LEFT((+RIGHT(I15,5)),1)))

I also have a side question regarding replacing numbers, without using the manual prompt.

I have a cell who generates a number lets say 8.5, but that is not posible to do by the way football points are scored, 10 makes more sense. The same would happen if you got 11.5, 10 or 13 are more likely. Then, 14,17,20,21,23,24,27,28,31, 33, 35, 38, 41.

So i would need say like a side chart, perhaps a Vlookup of all the values and what they should round to in the football world. I am just thinking there has to be a more efficient way.

Anyway, thanks to everyone that reads this.

Cheers

Lou

 

[jtakw]

Hi,

If you copied and pasted your data from a website, chances are that's Not a normal "Space" after the L, but a "non-breaking space", that's why using VALUE didn't work.

Try this:

Book1
	A	B	C
1	L 31-33	31	33
2	L 0-9	0	9
Sheet348

Cell Formulas
Range	Formula
B1	=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,CHAR(160)," "),"-"," ")," ",REPT(" ",30)),COLUMNS($B1:B1)*30,30)+0

Formula copied down and across.

Don't understand the 2nd part of your question...

 

[Lou Pointspreads]

AWESOME THIS SORCERY WORKED WONDERS!!! THANK YOU SO MUCH!


I follow the non breaking space, i had dealt with it once before. I remember the character 160 part.

What does the triple nested substitution do, or work? just to learn if you don't mind.

I follow the substitute the - hyphen with a blank, then " " the space, REPT?

what does that part do? REPT(" ", 30) what does the 30 mean?

Columns i get, but then *30,30)+0.

I guess the 30 is throwing me off, i didn't follow after it.

The second part of the question was, how do you round a number to a pre set amount.

Let's say everytime there is a number from 0-8.5 you round to 7. Everytime there is a 9-11.5 you round to 10. 12-13.5 round to 13. 14-16.5 you round to 14. 17 to 19 you round to 17.

I was thinking of having all values on a two column chart. and linking it via Vlookup. But that seemed like an extra step.

Thanks for all the help.

Cheers,

Lou

Hi,

If you copied and pasted your data from a website, chances are that's Not a normal "Space" after the L, but a "non-breaking space", that's why using VALUE didn't work.

Try this:

	A	B	C
L 31-33
L 0-9

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]

</tbody>

Sheet348​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,CHAR(160)," "),"-"," ")," ",REPT(" ",30)),COLUMNS($B1:B1)*30,30)+0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down and across.

Don't understand the 2nd part of your question...

 

[jtakw]

Hi again,

Follow this thread for my explanation regarding the formula in Post # 2, pay special attention to Post # 22 here:

https://www.mrexcel.com/forum/excel...-how-can-nth-element-referenced-formulas.html

To answer your 2nd question, if you Don't want to build a table, use this per your description:

Book1
	A	B
4	8.5	7
5	10.5	10
6	12	13
7	16.5	14
8	18	17
Sheet348

Cell Formulas
Range	Formula
B4	=LOOKUP(A4,{0,9,12,14,17},{7,10,13,14,17})