Answered by:
Looking for R code that is the equivelant of an Excel formula
Question

Column A in my Excel sheet has time in text format  in hours minutes and seconds (<n>h <n>m <n>s format). I have an Excel formula that converts these values with the numeric equivalent. For eg,
 the value 1h 16m is converted to 1.27
 the value 0h 4m is converted to 0.07
 the value 24s is converted to 0
Excel formula: =(IFERROR(LEFT(A1,SEARCH("h",A1)1)*60,0)+IFERROR(MID(A1,SEARCH("m",A1)2,2),0))/60
What is the R code to do the same calculation?
Tuesday, December 6, 2016 11:02 PM
Answers

Hi Priya,
R provides the gsub() function to do search/replace type operations. The stackoverflow posting here should hopefully help you here:
http://stackoverflow.com/questions/11936339/inrhowdoireplacetextwithinastring
jmp
 Proposed as answer by jopelamsft Friday, December 9, 2016 4:39 PM
 Marked as answer by jopelamsft Monday, December 12, 2016 1:08 AM
Friday, December 9, 2016 4:36 PM
All replies

Hello,
You can try with this one :
minPerGame < c("1:16","0:4","0:0:24")
sapply(strsplit(minPerGame,":"),
function(x) {
x < as.numeric(x)
x[1]+x[2]/60
}
)Best regard !
Thursday, December 8, 2016 3:49 PM 
Thanks! To use this code, I will need to strip out h, m, and s along with the spaces in the data and replace it with :
Any tips on how to do that?
Thursday, December 8, 2016 5:41 PM 
Hi Priya,
Did you attempt and find/replace within Excel? That should help, I think.
jmp
Thursday, December 8, 2016 6:33 PM 
I can make it work in Excel, but I am trying to do all the data prep steps in Azure ML/R. That way I don't have to do any manual data cleanup each time the dataset changes. In my case, I will have a new dataset monthly. I want to overwrite the csv file that the Azure ML experiment is using and simply rerun the experiment.
Thursday, December 8, 2016 7:46 PM 
Hi Priya,
R provides the gsub() function to do search/replace type operations. The stackoverflow posting here should hopefully help you here:
http://stackoverflow.com/questions/11936339/inrhowdoireplacetextwithinastring
jmp
 Proposed as answer by jopelamsft Friday, December 9, 2016 4:39 PM
 Marked as answer by jopelamsft Monday, December 12, 2016 1:08 AM
Friday, December 9, 2016 4:36 PM