Formula to populate higher value between 2 cell.

[fkp7057]

Hello All,

I am trying to populate higher no. value from selected cell, which has some alphanumerical data.

as per below table.

Value A	Value B	Result I want
3.5Ax25	3.5Cx120	3.5Cx120
3.5Bx35	4Dx16	3.5Bx35
3.5Cx120	3.5Ax25	3.5Cx120
4Dx16	3.5Bx300	3.5Bx300

if you compare 1st raw it should be compare between 25 and 120 and result should be 3.5Cx120.

any help on this much apricated.

[#2]

@fkp7057 

Use below formula-

=IF(--MID(A2,SEARCH("x",A2)+1,100)>--MID(B2,SEARCH("x",B2)+1,100),A2,B2)

[#3]

Thanks for your prompt response.
but above formula is not working where cell conation some charter after number.
in my data there is number after "x" and i just want to compare and populate only higher no between those cells.
example :
3.5Cx120 admm-al 3.5Ax25 admm-cu
4Dx16 admm-al 3.5Bx300 admm-cu

[#4 · Accepted solution]

@fkp7057 

You should always provide relevant examples. Your first post didn't mention this option at all!

=IF(--MID(A2,FIND("x",A2)+1,IFERROR(FIND(" ",A2),LEN(A2))-FIND("x",A2))>--MID(B2,FIND("x",B2)+1,IFERROR(FIND(" ",B2),LEN(B2))-FIND("x",B2)),A2,B2)

If you have Microsoft 365 or Office 2021:

=LET(
    xPosA, FIND("x",A2),
    sPosA, IFERROR(FIND(" ",A2),LEN(A2)),
    numA, --MID(A2,xPosA+1,sPosA-xPosA),
    xPosB, FIND("x",B2),
    sPosB, IFERROR(FIND(" ",B2),LEN(B2)),
    numB, --MID(B2,xPosB+1,sPosB-xPosB),
    IF(numA>numB,A2,B2)
)