Weight Cell Convert

[MATU70]

Hi

I currently have a sheet i am working on that has four columns to convert from stones to pounds as excel does not recognise the format as there are 14 pounds in a stone and the data i am pasteing appears as (11-7), What i am having to do is e.g (11st x 14lbs + 7lbs = 161lbs) this is using four cells to do the calculation.
Is there any way i can format the column/cell to recognise when i input data 11-7 to automaticly recognise it and shows 161?

I only need between 8st & 12st.

Thanks

[:) Sixthsense :)]

Format the A1 Cell as Text

In A1 Cell

11-7

In B1 Cell

=IFERROR(SUM(LEFT(A1,FIND("-",A1)-1)*14,MID(A1,FIND("-",A1)+1,255)),"-")

[MATU70]

that does reduce the number of cells by half which is great in itself, but i was wondering if it was possible to format the cell i was in to recognise the data input and convert it to pounds all in the same cell once i leave that cell e.g i input 11-7 i leave cell, same cell shows 161?

[:) Sixthsense :)]

Copy the below code and do right click on sheet tab and select view code and paste it.

Close the VBA window (Alt+Q to close VBA window) and return to that sheet and check.

Please Login or Register  to view this content.

Enter the value in A1 Cell and check....

Ensure that your entry should start with a single quote ( ' ) so that the numeric entry will be considered as text otherwise excel will convert the entry to real date.

I restricted the conversion to happen in A1 cell alone.

[MATU70]

That is so cool Sixthsense, it works perfectly I just formatted whole column as text and in the code i gather i just change the A1 to where ever i need the code to run and copy formula all the way down?

I must say this saves a hell of alot of time and reduces space on sheet, lol its fitting that i watched the film Son Of God last night

Thanks alot

[:) Sixthsense :)]

Replace this line

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

That will make the code to work in any cell of Column_A


If you want to set it for Column B then change 1 to 2

[:) Sixthsense :)]

Or

If you want to restrict the event to occur on a specific range of cells then

Replace this line of code

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

Change the range reference C1:H15 to your desired range

[MATU70]

Magic +++++

[MATU70]

Just want to add that it works a dream with my Data validation drop down list, Fantastic work Sixthsense