converting text to time

[Keyrw01]

Good Morning

I am looking for a little help if possible.

I have data that needscoverting to an hh:mm:as format.

the data I am getting is in the following formats and need it all changing to same format as above.

22s
22m 22s
2h 22m 22s

any help would be much apreciated

 

[MARZIOTULLIO]

	A​	B​
1​	22s	00:00:22​
2​	22m 22s	00:22:22​
3​	2h 22m 22s	02:22:22​

<tbody>
</tbody>

B1=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",""),"m",""),"s","")," ","")+0,"00\:00\:00")+0 copy down

Custom format column B hh:mm:ss

 

[Keyrw01]

	A​	B​
1​	22s	00:00:22​
2​	22m 22s	00:22:22​
3​	2h 22m 22s	02:22:22​

<tbody>
</tbody>

B1=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",""),"m",""),"s","")," ","")+0,"00\:00\:00")+0 copy down

Custom format column B hh:mm:ss

First of all thank you very much for the reply.

I have tried the formula that you have provided but I get the error stating 'There's a problem with this formula'

 

[MARZIOTULLIO]

Can you post your file ?

 

[Keyrw01]

Can you post your file ?

unfortunately not. It has personal details of employees on there.

I have tried the same formula on a fresh spreadsheet and I'm getting the same issue.

im thinking I'm probably doing it wrong somehow

 

[Keyrw01]

Can you post your file ?

I have managed to get this working.

you are a star thank you very much

 

[Keyrw01]

Can you post your file ?

	A​	B​
1​	22s	00:00:22​
2​	22m 22s	00:22:22​
3​	2h 22m 22s	02:22:22​

<tbody>
</tbody>

B1=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",""),"m",""),"s","")," ","")+0,"00\:00\:00")+0 copy down

Custom format column B hh:mm:ss

the new issue that I am having is that if there is a single number of hours minutes and seconds it will not bring it out correctly.

an example would be 1h 1m 1s. this would be brought back as 00:01:11.
any help would be brilliant