Excel cell text need to separate based on days and months-CodePudding

I have cell value with 115y300d which needs to be move to separate cell, however in few cell I have data like 10h30m, so it's mixed text.

What I want to do is value before "y" should go in Year Column "d" in Days, similar for h = hours and M in Minutes. Since it is not in similar format, I'm not able to do text to columns and other functions, and need your help.

CodePudding user response：

You could use find() to do things like so:

=if(iferror(find("y",A2,1)>0,0),left(A2,find("y",A2,1)-1,"")

which will put the value before y into the cell or set it to blank. Expand the idea to find d & y etc

CodePudding user response：

One option could be:

Formula in B1:

=DROP(WRAPROWS(TEXTSPLIT(CONCAT(BYROW(A1:A5,LAMBDA(a,IF(RIGHT(a)="d",a&"hm","yd"&a)))),,{"y","d","h","m"},,""),4),-1)

If you hit CONCAT() limits, you can also do this by row (dragging):

=TEXTSPLIT(IF(RIGHT(A1)="d",A1&"hm","yd"&A1),{"y","d","h","m"},,,"")