How do I combine these 2 formulas…want to delete s, then use the changing text into minutes formula that I...
up vote
-1
down vote
favorite
original formula found on this site (BTW, thank you for this): 0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(A1,"*hour*"))&A1,"hours",":"),"minutes",""),",","")
My data contains mins, min, hrs, hr, so I want to delete the "s", I did it with:=SUBSTITUTE(G2,"s","")
Then I would use a modified version of the original formula
=0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(H2,"*hr*"))&H2,"hr",":"),"min",""),",","")
But now I can't find how to combine the two formulas in one cell
microsoft-excel worksheet-function
edited Nov 16 at 13:42
Máté Juhász
14.1k63150
asked Nov 16 at 13:39
lms
1
closed as unclear what you're asking by Máté Juhász, n8te, PeterH, Scott Craner, Mike Fitzpatrick Nov 16 at 23:33
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
-1
down vote
favorite
original formula found on this site (BTW, thank you for this): 0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(A1,"*hour*"))&A1,"hours",":"),"minutes",""),",","")
My data contains mins, min, hrs, hr, so I want to delete the "s", I did it with:=SUBSTITUTE(G2,"s","")
Then I would use a modified version of the original formula
=0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(H2,"*hr*"))&H2,"hr",":"),"min",""),",","")
But now I can't find how to combine the two formulas in one cell
microsoft-excel worksheet-function
edited Nov 16 at 13:42
Máté Juhász
14.1k63150
asked Nov 16 at 13:39
lms
1
closed as unclear what you're asking by Máté Juhász, n8te, PeterH, Scott Craner, Mike Fitzpatrick Nov 16 at 23:33
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1
Where exactly you've find the formula? What you want to achieve? What combine mean for you (display result of both? display one of them based on a condition?...)
– Máté Juhász
Nov 16 at 13:42
disregard...I found the nesting I needed =0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(G2,"hr"))&G2,"s",""),"hr",":"),"min",""),",","")
– lms
Nov 16 at 14:29
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
original formula found on this site (BTW, thank you for this): 0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(A1,"*hour*"))&A1,"hours",":"),"minutes",""),",","")
My data contains mins, min, hrs, hr, so I want to delete the "s", I did it with:=SUBSTITUTE(G2,"s","")
Then I would use a modified version of the original formula
=0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(H2,"*hr*"))&H2,"hr",":"),"min",""),",","")
But now I can't find how to combine the two formulas in one cell
microsoft-excel worksheet-function
edited Nov 16 at 13:42
Máté Juhász
14.1k63150
asked Nov 16 at 13:39
lms
1
original formula found on this site (BTW, thank you for this): 0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(A1,"*hour*"))&A1,"hours",":"),"minutes",""),",","")
My data contains mins, min, hrs, hr, so I want to delete the "s", I did it with:=SUBSTITUTE(G2,"s","")
Then I would use a modified version of the original formula
=0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(H2,"*hr*"))&H2,"hr",":"),"min",""),",","")
But now I can't find how to combine the two formulas in one cell
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Nov 16 at 13:42
Máté Juhász
14.1k63150
asked Nov 16 at 13:39
lms
1
edited Nov 16 at 13:42
Máté Juhász
14.1k63150
asked Nov 16 at 13:39
lms
1
edited Nov 16 at 13:42
Máté Juhász
14.1k63150
edited Nov 16 at 13:42
Máté Juhász
14.1k63150
edited Nov 16 at 13:42
Máté Juhász
14.1k63150
14.1k63150
asked Nov 16 at 13:39
lms
1
asked Nov 16 at 13:39
lms
1
asked Nov 16 at 13:39
lms
1
1
closed as unclear what you're asking by Máté Juhász, n8te, PeterH, Scott Craner, Mike Fitzpatrick Nov 16 at 23:33
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as unclear what you're asking by Máté Juhász, n8te, PeterH, Scott Craner, Mike Fitzpatrick Nov 16 at 23:33
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1
Where exactly you've find the formula? What you want to achieve? What combine mean for you (display result of both? display one of them based on a condition?...)
– Máté Juhász
Nov 16 at 13:42
disregard...I found the nesting I needed =0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(G2,"hr"))&G2,"s",""),"hr",":"),"min",""),",","")
– lms
Nov 16 at 14:29
add a comment |
1
Where exactly you've find the formula? What you want to achieve? What combine mean for you (display result of both? display one of them based on a condition?...)
– Máté Juhász
Nov 16 at 13:42
disregard...I found the nesting I needed =0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(G2,"hr"))&G2,"s",""),"hr",":"),"min",""),",","")
– lms
Nov 16 at 14:29
1
1
Where exactly you've find the formula? What you want to achieve? What combine mean for you (display result of both? display one of them based on a condition?...)
– Máté Juhász
Nov 16 at 13:42
Where exactly you've find the formula? What you want to achieve? What combine mean for you (display result of both? display one of them based on a condition?...)
– Máté Juhász
Nov 16 at 13:42
disregard...I found the nesting I needed =0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(G2,"hr"))&G2,"s",""),"hr",":"),"min",""),",","")
– lms
Nov 16 at 14:29
disregard...I found the nesting I needed =0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(G2,"hr"))&G2,"s",""),"hr",":"),"min",""),",","")
– lms
Nov 16 at 14:29
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Where exactly you've find the formula? What you want to achieve? What combine mean for you (display result of both? display one of them based on a condition?...)
– Máté Juhász
Nov 16 at 13:42
disregard...I found the nesting I needed =0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0:",1-COUNTIF(G2,"hr"))&G2,"s",""),"hr",":"),"min",""),",","")
– lms
Nov 16 at 14:29