How to convert hour and minute using excel function only?

[Falinaicare]

Dear Expert,

How to convert hour and minute by using function only from column E (e.g. 202H25M) to Column G (e.g. 202:25)?

Excel uploaded.

Thanks.

 

[AlanSidman]

I used Power Query/Get and Transform to achieve this.

Mcode of that action:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({"H","M"}, QuoteStyle.None), {"Column1.1", "Column1.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Hour:Minutes")
in
    #"Merged Columns"

Data Range

	A​
1​	Hour:Minutes​
2​	202:25​
3​	100:20​

 

[Fluff13]

How about
=SUBSTITUTE(SUBSTITUTE(E5,"H",":"),"M","")+0

 

[GraH - Guido]

Haha @Fluff13 , that's simply elegant and so obvious it hearts. I still have the reflex to think left, find, mid, find, ... and thus like @AlanSidman quickly resort to PQ

 

[Falinaicare]

I used Power Query/Get and Transform to achieve this.

Mcode of that action:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({"H","M"}, QuoteStyle.None), {"Column1.1", "Column1.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Hour:Minutes")
in
    #"Merged Columns"

Data Range

	A​
1​	Hour:Minutes​
2​	202:25​
3​	100:20​

Dear AlanSidman

I will look into it as I am totally new in Power Query.

https://www.poweredsolutions.co/2015/01/23/power-query/ is a new modern of excel.

Thank you.

 

[Falinaicare]

Dear Fluff13

Thanks for your simple function in excel. This is what I am looking for the answer.

How about
=SUBSTITUTE(SUBSTITUTE(E5,"H",":"),"M","")+0

 

[Fluff13]

Glad we could help & thanks for the feedback.