textafter and textbefore function alternate in excel-2016

[AFZAL SOHAIL]

Dear Team,
I want to know what is alternate of textafter and textbefore function in excel-2016
I see a formula in this page;

Cell Formulas
Range	Formula
K2:K3	K2	=TEXTBEFORE(J2,"h ")*60+TEXTAFTER(TEXTBEFORE(J2,"m"),"h ")

I want to know the alternate in excel 2016
Thanks a lot for your reply please..

 

[etaf]

can you give an example of the TEXT you want to use those functions on
and also the result expected
that does not have text with "h " or "M"
seems to be some sort of Time manipulation based on text that has so many hours and mins
ie

6h 5m
and then converts to minutes
But just guessing

=TEXTBEFORE(J2,"h ")*60 this bit - multiplies the hours by 60 to get minutes TEXTAFTER(TEXTBEFORE(J2,"m"),"h ") will extract just the minutes and add them - so the number between the h and m

so
=LEFT(A2,FIND("h",A2)-1)*60
will find the Hour part
and then maybe
=MID(A2,FIND("h ",A2)+2,(FIND("m",A2))-(FIND("h ",A2)+2))
for the mins


=(LEFT(A2,FIND("h",A2)-1)*60)+(MID(A2,FIND("h ",A2)+2,(FIND("m",A2))-(FIND("h ",A2)+2))*1)

Book4
	A	B	C	D
1
2	1h 5m	60	5	65
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LEFT(A2,FIND("h",A2)-1)*60
C2	C2	=MID(A2,FIND("h ",A2)+2,(FIND("m",A2))-(FIND("h ",A2)+2))
D2	D2	=(LEFT(A2,FIND("h",A2)-1)*60)+(MID(A2,FIND("h ",A2)+2,(FIND("m",A2))-(FIND("h ",A2)+2))*1)

 

[AFZAL SOHAIL]

Sir you are right guessing.
some example are there and the results:-

1h 30m	90​
21h 0m	1260​
3h 10m	190​

 

[etaf]

did you try the formula i gave
=(LEFT(A2,FIND("h",A2)-1)*60)+(MID(A2,FIND("h ",A2)+2,(FIND("m",A2))-(FIND("h ",A2)+2))*1)

Book7
	A	B	C
1
2	1h 30m	90	90
3	21h 0m	1260	1260
4	3h 10m	190	190
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=(LEFT(A2,FIND("h",A2)-1)*60)+(MID(A2,FIND("h ",A2)+2,(FIND("m",A2))-(FIND("h ",A2)+2))*1)

 

[AFZAL SOHAIL]

Thanks a lot Sir,
Your formula is working very well.

 

[FormR]

Hi, just another option if you're interested.

Book1
	A	B	C
1
2	1h 30m	90	90
3	21h 0m	1260	1260
4	3h 10m	190	190
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=SUBSTITUTE(LEFT(A2,LEN(A2)-1),"h",":")*24*60

 

[AFZAL SOHAIL]

Thanks a lot Sir FORM R,
This is also a very nice and simple option Sir..

 

[AFZAL SOHAIL]

Sir if it is in this form than what will be formula?

2 hr. 09 min.

3 hr. 00 min.

1 hr. 53 min.

 

[FormR]

My suggestion would look like this:

Book1
	A	B
1
2	2 hr. 09 min.	129
3	3 hr. 00 min.	180
4	1 hr. 53 min.	113
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=SUBSTITUTE(LEFT(A2,LEN(A2)-4),"hr.",":")*24*60

 

[AFZAL SOHAIL]

Thanks a lot Sir FORM R,
This is also working very well..