More Text Manipulation

• Please download the worksheet.
• Last time we hit a dead end because the dataset is mostly text.
• Please make a copy of the Raw Data Records worksheet
  • Call this Records
• Can we compute the furthest distance in ft for each record?
  • Select B2:b11 and do a text to columns.
    • Split on the '
    • 
  • We could do two more splits, but there is another way.
• The mid function will extract the middle of a text.
• The numbervalue function will turn a text into a number (if it can)
• Let's combine these to get the length in inches out of column C.
• mid
  • Takes a text
  • A start position, where the first letter in the text is at position 1
  • The number of characters you want to extract
• So what do we want out of - 0.00"?
  • IN d2 try =mid(c2, 1,3)
  • Didn't work, what went wrong?
  • Change the starting position until you get the number you want.
  • 
  • 
• Now we have the problem that the value is a text not a number.
  • =numbervalue(cell) will fix this
  • 
  • 
• Finally compute the total length of the record in feet.
  • 
  • 
• Make a copy of the Raw Data Athletes called Cleaned Athletes.
• Can you compute the distance in FT for the Braemar Stone Throw for each athlete?
  • This is more of a problem since we don't know the length of the number.
  • But the length function can help.
  • We really just want the length of the original string -2.
  • =mid(text, 2, len(text)-2)
  • 
  • 
• Can you rank the athletes by distance in the Braemar Stone Throw?
• 
• 
• Can you repeat this process for the Open Stone Throw?