Display "0" & Extract/Display Specific Numbers in Another Cell

[Disarmonious]

Hello,

Two-part question;

• I would like cell DU45 to include the number 0 in front of the first number as part of a result of converting time into a decimal number. Cell DU45 has this formula; =(CT45*60)+(CV45/60). CT45 converts numbers of hours into minutes and then adds the minutes entered in cell CV45 which converts minutes into decimals, or something like that. So if the user enters 01 (hour) in cell CT45, and then enters 15 (minutes) in cell CV45, cell CU45 displays 60.25, but I would like it to display 060.25. Is this possible?
• I have 5 separate cells that are designed to extract single resulting numbers from cell CU45, and I’ve already figured out how to extract the first number 0 by using the =LEFT formula which is applied to cell CX45, and the last number 5 by using the =RIGHT formula which is applied in cell DB45, but I would like cell CY45 to display the 2nd number; “6”, CZ45 to display the 3rd number; “0”, and DA45 to display the 4th number; “2”.

 

[svendiamond]

1. You could simply write ="0"&DU45 or your formula... that would force a zero in front - but it would change the data type to text

2. Yes just use =MID(CU45, 2, 1) or =MID(CU45, 3, 1)

 

[Eric W]

For your first question, you could apply a custom format of 000.00

 

[Disarmonious]

For your first question, you could apply a custom format of 000.00

I used your suggestion, and applied DU45 as a custom format to 000.00, but the number "6" displays when I apply the formula =LEFT(DU45, 1) in cell CX45 after I enter 1 hour in cell CT45, and 10 minutes in cell CV45. I'm happy that DU45 displays "060.17", but I'm wondering why 0 is not being considered as the first number from cell CX45? Any suggestions?

 

[Disarmonious]

Hello,

Two-part question;

• I would like cell DU45 to include the number 0 in front of the first number as part of a result of converting time into a decimal number. Cell DU45 has this formula; =(CT45*60)+(CV45/60). CT45 converts numbers of hours into minutes and then adds the minutes entered in cell CV45 which converts minutes into decimals, or something like that. So if the user enters 01 (hour) in cell CT45, and then enters 15 (minutes) in cell CV45, cell CU45 displays 60.25, but I would like it to display 060.25. Is this possible?
• I have 5 separate cells that are designed to extract single resulting numbers from cell CU45, and I’ve already figured out how to extract the first number 0 by using the =LEFT formula which is applied to cell CX45, and the last number 5 by using the =RIGHT formula which is applied in cell DB45, but I would like cell CY45 to display the 2nd number; “6”, CZ45 to display the 3rd number; “0”, and DA45 to display the 4th number; “2”.

Thank you for your suggestion in using =MID(CU45, 2, 1) or =MID(CU45, 3, 1), which were the results I was looking for. I may have messed up my wording as I stated that cell CU45 only displayed 60.25, but I was referring to cell DU45 displaying that number after calculating =(CT45*60)+(CV45/60). I used the suggestion that I got from another reply of using the custom format 000.00 for cell DU45, which worked for that resulting cell, but when I apply the formula =LEFT(DU45, 1) in cell CX45, the number "0" doesn't display, it displays the 2nd number, "6", so I'm wondering how I can get this =LEFT formula to read the "0" as the first "number"? Any suggestions? In reiteration, in cell CX45; after I enter 1 hour in cell CT45, and 10 minutes in cell CV45, DU45 displays "060.17", but I'm wondering why 0 is not being considered as the first number from cell CX45 when using the =LEFT formula?

 

[Disarmonious]

1. You could simply write ="0"&DU45 or your formula... that would force a zero in front - but it would change the data type to text

2. Yes just use =MID(CU45, 2, 1) or =MID(CU45, 3, 1)

Thank you for your suggestion in using =MID(CU45, 2, 1) or =MID(CU45, 3, 1), which were the results I was looking for. I may have messed up my wording as I stated that cell CU45 only displayed 60.25, but I was referring to cell DU45 displaying that number after calculating =(CT45*60)+(CV45/60). I used the suggestion that I got from another reply of using the custom format 000.00 for cell DU45, which worked for that resulting cell, but when I apply the formula =LEFT(DU45, 1) in cell CX45, the number "0" doesn't display, it displays the 2nd number, "6", so I'm wondering how I can get this =LEFT formula to read the "0" as the first "number"? Any suggestions? In reiteration, in cell CX45; after I enter 1 hour in cell CT45, and 10 minutes in cell CV45, DU45 displays "060.17", but I'm wondering why 0 is not being considered as the first number from cell CX45 when using the =LEFT formula?

 

[Eric W]

I used your suggestion, and applied DU45 as a custom format to 000.00, but the number "6" displays when I apply the formula =LEFT(DU45, 1) in cell CX45 after I enter 1 hour in cell CT45, and 10 minutes in cell CV45. I'm happy that DU45 displays "060.17", but I'm wondering why 0 is not being considered as the first number from cell CX45? Any suggestions?

When you apply a custom format, it only affects how the number is displayed, it doesn't change the underlying value. If that value is 60.17, you'll see 060.17, but the first digit really is 6. You can get around that by using TEXT before MID, like so:

Book1
	M	N	O	P	Q	R	S
2	060.25	6
3		0	6	0	.	2	5
Sheet5

Cell Formulas
Range	Formula
N2	N2	=LEFT(M2,1)
N3:S3	N3	=MID(TEXT($M$2,"000.00"),COLUMNS($N3:N3),1)

Use the same format in TEXT that you use as a custom format.

Probably the biggest question is how you intend to use these values. Are they for display only, or do you intend on using them in calculations? That could affect the kind of formula we suggest.