Excel help

[Vox Populi Vox Dei]

Hello fellow forumites

I'm wondering if you could help with a slight problem:



Basically I am interested in calculating the total fees per year (someone else did the data capturing).

Based on the following assumptions:

1. 2 terms per year
2. R2 = £1
3. Only males were enrolled
4. 5 shillings = 1/4 quarter of a pound:

I came up with the following figure for first-year 1919 BA degree:

=2*((2*5)+2+1+2*0.25+3*1)

= 33

Obviously I want to automate this process, however, there is text in three of the cells which have numbers. I think I know of a way to extract numbers from a text string, however I'd then have to do it for many hundreds of cells. Is there rather a way, when constructing a formula, for Excel to just just extract the numbers?

Thanks

 

[MrR]

Why not replace those string values with just the numerical values, e.g. instead of having $5 per year under course fees, make it $5 under course fees per year?

Secondly, with the formula, rather use a table with the conversion values in. That way, when you decide that 5 shillings = half a pound, you don't have to update all your formulae, just the conversion values in the table.

 

[DrJohnZoidberg]

Best way would be to remove the text from those fields and then use formatting to display it like you want.

Because you know exactly what characters are being used you can just use a find/replace and remove the characters.

Second option if you don't want to do that is to use a trim formula, but that all depends on how the data is being displayed.

Third option is to just extract the numbers from your cells as described here: http://www.extendoffice.com/documents/excel/1622-excel-extract-number-from-string.html but this is going to complicate things.

Always try to work with clean source data first, if you cannot get it cleaned then only rely on other "hacks."

 

[MrR]

Another suggestion, if you can rebuild the table, rather do it using another format. Something like:



Should help with presentation layer, e.g. analysis (pivots, filtering), charting, etc.

 

[Vox Populi Vox Dei]

Another suggestion, if you can rebuild the table, rather do it using another format. Something like:

View attachment 170235

Should help with presentation layer, e.g. analysis (pivots, filtering), charting, etc.

That's an excellent idea...but would involve me having to rebuild from 1919-2013! But that would make it far easier, and would impress my supervisor who would be able to see the calculations...

 

[Y'all Qaeda]

So use the left command to sort out the numbers, given that they seem to always be infront and then hack the formula to figure out the totals.

 

[SeRpEnT]

Column B
ROW 3: £5 per term

=RIGHT((LEFT(B3,2)),1)

Would return a value of 5.
That's the easy part.