Excel If-Else formula to capture ''hour“ and ”min" across an excel row

[Mithun.Uchil]

I have a row of "h min" values (eg:2h 15min) across a row. I need to capture the 'hour' and 'min' separately for which I'm using "=LEFT(B2,(FIND(" ",B2,1)-1))" and "=MID(B2,FIND(" ",B2)+1,256)" in separate cells, using the space separator to identify them seperately.

The problem is when there is only either one value, i.e. only hour or only minute (eg. "2h" or "20min") in which case I get an error. How should I re-code both my LEFT and MID formula with IF-ELSE to ensure it still captures the hour/minute where it's present and gives a blank where it's not present.

Many thanks.

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Mithun.Uchil]

Thanks. Attached a workbook for reference.

[FDibbins]

Try these...
hour =IFERROR(LEFT(B2,SEARCH("h",B2)),"")
min =IFERROR(MID(B2,FIND(" ",B2)+1,99),"")

[Mithun.Uchil]

Hi FDibbins,

Thanks, this worked for the hour, but does not work where only 'min' is there since there is no space before 'min' in this case, hence the FIND " " does not work there. Any roundabout for this??

Updated my excel with your formula which works for the hour but not the minute.

Many, many thanks again from again from a newbie here.

[bebo021999]

B3:
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(SEARCH("h",B2)),"","h")&B2&IF(ISNUMBER(SEARCH("min",B2)),"","min"),"h",REPT(" ",20)),"min",REPT(" ",20)),20))&IF(ISNUMBER(SEARCH("h",B2)),"h","")

B4:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(SEARCH("h",B2)),"","h")&B2&IF(ISNUMBER(SEARCH("min",B2)),"","min"),"h",REPT(" ",20)),"min",REPT(" ",20)),20,20))&IF(ISNUMBER(SEARCH("min",B2)),"min","")

[Mithun.Uchil]

Thanks!! This one works bebo021999

[FDibbins]

Try this for the MIN part...
=IFERROR(MID(B2,FIND(" ",B2)+1,99),IF(AND(ISERROR(FIND(" ",B2)),NOT(ISERROR(FIND("min",B2)))),B2,""))