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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]22s[/TD]
[TD]

00:00:22​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]22m 22s[/TD]
[TD]

00:22:22​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]2h 22m 22s[/TD]
[TD]

02:22:22​

[/TD]
[/TR]
</tbody>[/TABLE]


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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]22s[/TD]
[TD]

00:00:22​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]22m 22s[/TD]
[TD]

00:22:22​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]2h 22m 22s[/TD]
[TD]

02:22:22​

[/TD]
[/TR]
</tbody>[/TABLE]


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 ?

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]22s[/TD]
[TD]

00:00:22​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]22m 22s[/TD]
[TD]

00:22:22​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]2h 22m 22s[/TD]
[TD]

02:22:22​

[/TD]
[/TR]
</tbody>[/TABLE]


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