# 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/in-r-how-do-i-replace-text-within-a-string

jmp

• Proposed as answer by Friday, December 9, 2016 4:39 PM
• Marked as answer by 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 re-run 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/in-r-how-do-i-replace-text-within-a-string

jmp

• Proposed as answer by Friday, December 9, 2016 4:39 PM
• Marked as answer by Monday, December 12, 2016 1:08 AM
Friday, December 9, 2016 4:36 PM