Forum Home
Press F1
Thread ID: 79576	2007-05-25 00:21:00	Excel question	yorkshirekid (9033)	Press F1

Post ID	Timestamp	Content	User
552829	2007-05-25 00:21:00	In a cell I have a metric measure I want to convert to imperial in a seperate cell. Example: in A1 I have 5.84 [feet]. In B1 I want to display this as 5' 10". How do I do this please?	yorkshirekid (9033)
552830	2007-05-25 05:55:00	Yorkshire kid: a metric measure to convert to imperial 5.84 [feet]. to display 5' 10". Make up your mind lad. 5.84 feet is not metric, it's simply imperial expressed as a decimal. Do you mean metric to imperial or do you mean decimal feet to feet and inches?	coldot (6847)
552831	2007-05-25 06:02:00	Yes, this is just arithmetic, not metric/imperial unit conversion. ;) All you need to do is separate the integer and fraction parts of the input. There will be a function (INT?) to get the integer part and if there isn't one for the fraction , all you need is a subtraction: INT(5.84) --> 5, 5.84-5 --> 0.84 . then your answer is 5 ft and 0.84x12 inches. You might want to truncate or round the "inches" to 2 digits.	Graham L (2)
552832	2007-05-25 06:15:00	If you mean what I think you mean, try this in B1: =CONCATENATE(TEXT(INT(A1),"0"),"'",TEXT((12*(A1 - INT(A1))),"00"),CHAR(34))	coldot (6847)
552833	2007-05-25 06:18:00	That's what I said. :D But I didn't say it in EXCELtalk, I left that as an exercise for the reader ;)	Graham L (2)
552834	2007-05-25 06:21:00	True, but I was guessing that he's not very Exceliterate. And if it's not people heights and they go over 9ft 11in he'll need "00" or "000" etc in that first text expression. Cheers!	coldot (6847)
552835	2007-05-25 06:30:00	True, but I was guessing that he's not very Exceliterate. And if it's not people heights and they go over 9ft 11in he'll need "00" or "000" etc in that first text expression. Cheers!Can you do no formatting on that ... the feet value is an integer, so you want all the digits. For the inches, I just suggested formatting, because you don't usually care about fractional bits of inches e.g. 10.08" . Perhaps just do an INT() on that too ... (or a round?).	Graham L (2)
552836	2007-05-25 08:50:00	You two are excellent ;-) Ok, firstly my apologies for not being clear. Yes, I knew "...5.84 feet is not metric, it's simply imperial expressed as a decimal." And I guess what I meant was "...decimal feet to feet and inches". So, the CONCATENATE formula was EXACTLY what I wanted. Thank you both. I pasted it in and that did the trick. I just didn't know how to return the 5foot as 5' and then the decimal inches as 10". I could do the maths, not the CONCATENATE. This I don't follow. Now, could you please explain what the formula is saying (like a sentence) and that would be a help. Thanks to both of you again.	yorkshirekid (9033)
552837	2007-05-25 08:54:00	Well that was a quickfire response "...try this...", but I used formatting in order to turn it all into text so that I could insert concatenate the ft' and ins" symbols. Maybe some more Excelerated mathematical type could refine it for you, but in my archaic Excel version there doesn't seem to be a standard format for ft and inches. And anyway it seems the yorkshirekid hasn't come looking for it yet. Cheers PS Ooops - that was response to Graham. But hello ('ow do?) yorkshire kid. Graham pretty well put it in a sentence. I'll pop back with my version shortly.	coldot (6847)
552838	2007-05-25 09:25:00	If it’s any help - I created this by doing each step separately in new columns till I got the right solution, and then gradually added them together. As you’ll guess I’m not an Excel whizz, just an occasional user. Because you want feet and inches you need to separate those two parts Get the number of whole feet: take integer of a1 int(a1) = 5 Take the remainder by subtracting that integer from a1 (a1 - int(a1)) = 0.84 And multiply that fraction by 12 to turn it into inches 12*(a1 - int(a1)) = 10.08 In order to format with ‘ and “ (or we could use ft and in) turn all these numbers into text That’s because you can’t easily mix numbers ‘values’ with text, computers don’t like it Concatenate (put together several bits of text stuff separated by commas) Format for text: text( this number, in this format) the “00” tells how many whole numbers or you could use “00.00” to give whole numbers and 2 places of decimal if you don’t want to lose that 0.08. Text (int(a1),”0”) formats the number 5 into a text 5 (only one digit required in this formula) Add in a text apostrophe – put it between inverted commas to indicate that it’s text “’” That’s a “ and then a ‘ and then a “ Add in the text for number of inches text(12*(a1 - int(a1))) Add in the text double apostrophe – but you can’t use 3 of them together, computers don’t like that. So trick the computer by using chr(34) which ends up as a “ anyway That’s about it – but always count your brackets! Every opening bracket requires a closing bracket in the right place! And that where constructing it in separate stages (columns) is good tactic if your not sure. Hope that helps – it’s a bit longer than I intended – but I’m trying to clarify all the way. And is come on some Excel whizz out there who show us a much smaller formula that does the same thing.....	coldot (6847)
1 2