Changing time and text format

  • Thread starter Thread starter Jamie
  • Start date Start date
J

Jamie

I have data that is in a word document and it is written "37 Minutes". When
i copy that data into a cell in excel I want it to just show "37". I have
tried different formatiing option but to no avail. I also have a field
written out "1 Hour and 33 minutes" and want to convert that as well.

Any ideas?
 
<"1 Hour and 33 minutes" and want to convert that as well.>

convert to what?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have data that is in a word document and it is written "37 Minutes". When
| i copy that data into a cell in excel I want it to just show "37". I have
| tried different formatiing option but to no avail. I also have a field
| written out "1 Hour and 33 minutes" and want to convert that as well.
|
| Any ideas?
 
=LEFT(A1,FIND(" ",A1)-1)/(24*60) for the first.

=LEFT(A4,FIND(" ",A4)-1)/24+MID(A4,FIND("and",A4)+4,2)/(24*60) for the
second.

If you don't know which input cells will be in which format, you could try
=IF(ISNUMBER(FIND("Hour",A1)),LEFT(A1,FIND("
",A1)-1)/24+MID(A1,FIND("and",A1)+4,2)/(24*60),LEFT(A1,FIND("
",A1)-1)/(24*60))
 
4 minutes
18 minutes
2 minutes
3 minutes
6 minutes
1 minute
1 minute
2 minutes
1 hour 9 minutes
1 minute
22 minutes
1 minute
15 minutes
31 minutes
3 minutes
25 minutes
10 minutes
5 minutes
30 minutes

Nope that did not work. The examples above is the data I am pulling from
word. When I cpoy it into the cell and try to format it nothing happens.
Any other ideas?
 
Back
Top