change hours

[pranamu]

I want below sheet in only numerical format. if cell has given 9HRS then make it only 9 in the cell.

anyone can provide me formula.

<TABLE style="WIDTH: 97pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=129 border=0 x:str><COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=129 height=17>9HRS</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2HRS</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2HRS</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2HRS</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2HRS</TD></TR></TBODY></TABLE>

 

[buxtongt]

Do all of the cells end in "HRS"?

If so this should work:

=LEFT(A1,LEN(A1)-3)

where your data is in A1. Hope this helps.

 

[VoG]

Try

=LEFT(A1,LEN(A1)-3)+0

 

[pranamu]

Try

=LEFT(A1,LEN(A1)-3)+0

what if cell has given 3.5HRS or 12HRS??

 

[buxtongt]

Ah, good call VoG, the +0 formats it as a number.

 

[VoG]

what if cell has given 3.5HRS or 12HRS??

It still works

Excel Workbook
	A	B
1	9HRS	9
2	3.5HRS	3.5
3	12HRS	12
Sheet3

 

[buxtongt]

what if cell has given 3.5HRS or 12HRS??

This formula will drop the last three characters from any string of numbers/text. The +0 on VoG's formats it as a number.