# Parsing number from cell based on surrounding text

I want to make a spreadsheet wherein I type a number on a row, and the correct column displays the running total of all the numbers intended for that column.

This is simple to do with pairs of columns: put the number in the correct "helper" column and have a SUM formula copied down each of the main columns. But I don't want to use so many extra columns.

I previously asked (https://powerusers.codidact.com/posts/286067) if it was possible without **any** extra columns. I wanted to just type the number in the appropriate column, but have it display the sum instead of the number. However, there doesn't seem to be a good way of doing this.

I now want to know how I can use just one extra column for the whole sheet. I want to type something along the lines of "a-5c+5" in one cell and have formulas in the other columns that each check for their corresponding letter, and only sum the correct number.

What formula can I use to do this? I need to look for the appropriate substring and then parse the number, which could be positive or negative, integer or decimal. I don't have to use the format "a-5c+5"; if it's easier, I can switch it to "-5a5c" or "a-5ac+5c" or some other layout that might be easier to parse.

## 1 comment thread