• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Removing text from text and number string.

V

Vic14

New Member
Hi all, Thanks in advance for your help. I am a newbie to this type of thing so please be patient.

Have have a column of data which is around 30,000 rows. This data will get updated fairly frequently so I was hoping I could find a formula to solve my problem, It contains data held as text which represents time.

e.g.
1h3m represents 1hour and 3 minutes
34m represents 34 minutes etc
(there are no double digit hours e.g. 10h and there are no weeks e.g. 5w3d4h2m - which hopefully makes it easier)

I want to try to convert the data into a value so that I can then look at average times etc.

So far I have tried a substitute function which works well for the minutes (where I substitute "m" with "")

e.g.
34m becomes 34
but
1h3m becomes 1h3

So I tried a nested substitute function which changed the h to a 0 and then removed the space left

so
34 became 34
and 1h3m became 103

I am now stuck.

The only way through that my little brain can see is some logic where if the value is more than 3 numbers the multiply the numbers before 0 by 60 and add to the numbers after the first 0.

Therefore
34 would stay the same (less than 3 numbers)
1h3m goes to 103 then (1X60)+3 = 63mins
6h10m goes to 6010 then (6X60)+10 = 370 mins

Firstly I don't know if this is possible (or the formulae). Secondly, I feel that there must be a simpler way.

Thanks
 
Welcome to Chandoo. Org forums.

Are your cases limited to hours and minutes only?
Are all cells having consistency in the input i.e. 1h10m and no 1hour10min?

For posted sample see if below approach works:
=(IFERROR(LEFT(A1,SEARCH("h",A1,1)-1),0)*60)+SUBSTITUTE(MID(A1,SEARCH("h",A1,1)+1,99),"m","")
 
Thank you.

Every good answer generates another question (my inadequate question originally)...

That works really well. There are a few rows where no data is recorded and therefore the formula calculates #VALUE!.

Is there a way to for it to show no value so that I can perform basic averages (and use pivot tablets with all the other columns) etc on the data. I have uploaded my workbook.

Thanks again
 

Attachments

  • Duration data.xlsx
    62.8 KB · Views: 5
You must log in or register to reply here.
Back
Top