Entering old shilling currency in Excel

[bsmith]

I am working with old shillings & pence data in Excel which has the format - 1 s. 6 p. (=1 shilling 6 pence) where 12 pence = 1 shilling. It is a list of years, occupations and wages and I need to be able to sort on the currency ascending and also graph the trend of wages. But how do I make Excel understand the value of the shillings and pence currency?<br>
<br>
PS, I am not an Excel expert at all! Thanks for any tips.

 

[misterstick]

put the pounds, shillings and pence into seperate columns.<br><br><b><FONT FACE=monospace><br>column a: 1s. 6p.<br>column b: =VALUE(LEFT(A1, SEARCH(&quot;s&quot;,A1) - 1))<br>column c: =VALUE(MID(L135, SEARCH(&quot;s&quot;, L135) + 2, SEARCH(&quot;p&quot;, L135) - SEARCH(&quot;s&quot;, L135) - 1))<br>column d: =B1 + C1/12<br></font></b><br><br>column d then becomes a number (in shillings) you can use for calculations.<br>to translate A1 back into your format, use:<br><br><b><FONT FACE=monospace><br>=ROUNDDOWN(A1,0)&&quot;s. &quot;&ROUNDDOWN((A1-ROUNDDOWN(A1,0))*12,0)&&quot;p.&quot;<br></font></b><br><br>to calculate the pounds as well, use:<br><br><b><FONT FACE=monospace><br>=IF(A1&gt;20,ROUNDDOWN(ROUNDDOWN(A1,0)/20,0)&&quot;l. &quot;&MOD(ROUNDDOWN(A1,0),20),ROUNDDOWN(A1,0))&&quot;s. &quot;&ROUNDDOWN((A1-ROUNDDOWN(A1,0))*12,0)&&quot;p.&quot;<br></font></b><br><br>use the shilling (ie decimal) figure for the data on the chart, and the text representation for the data labels.<br><br>hope this helps.<br> <p>mr s. <<br><a href=mailto: > </a><br><a href= > </a><br>why does it never do what is says it does in the manual?