Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
Notifications
Mark all as read
Q&A

Parsing number from cell based on surrounding text

+1
−0

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.

Spreadsheet with one cell containing "a-5c+5", and columns labeled "a" and "c" which subtract and add 5 respectively from the previous totals in those columns

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.

Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

2 comment threads

Excel helper functions (1 comment)
Maybe have a look at https://exceljet.net/formula/if-cell-contains (1 comment)

0 answers

Sign up to answer this question »

This community is part of the Codidact network. We have other communities too — take a look!

You can also join us in chat!

Want to advertise this community? Use our templates!

Like what we're doing? Support us! Donate