Search code examples
excelsubstringformula

Excel Spreadsheet: Removing all data from cells in a square bracket

I have the following data in an excel spreadsheet.

Excel Spreadsheet of scores

The first data entry in each cell is contained within square brackets and contains the student's first attempt at a test. The second percentage is their final score.

I have been trying to find a formula that will easily remove all the brackets and data to leave only the final percentage in the cells. I want to do this so that I can then find the average of all the scores, and currently due to the data in the square brackets, I cannot use a formula.

What I've tried:

Data > Text to Columns > Tried to cut off at the 0, but I was either doing it wrong, or it didn't work.

I also looked at extracting (removing) the first four characters, but wasn't clear how to replace that with the final percentage result in the same cell itself.

Solution

  • Rethink your logic.
    A formula cannot remove content from a cell. For that you need a VBA macro.

    However, you can write a formula that ignores cell content.

    So, if myRange is the range over which you want to average. Perhaps D2:O2?

    =AVERAGE(--MID(myRange,FIND("]",myRange)+1,99))

    We FIND the location of the closing bracket, and add 1 to that for the possible start of our desired number.

    MID will return that number as a string, so the double unary -- converts it to a real number.

    Then AVERAGE.

    This is an array formula and, in earlier versions of Excel, may need to be entered with ctrl+shift+enter.