Forum Discussion

fkp7057's avatar
fkp7057
Copper Contributor
Jul 27, 2022
Solved

Formula to populate higher value between 2 cell.

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.5Cx...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jul 27, 2022

    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)
)
Harun24HR's avatar
Harun24HR
Bronze Contributor
Jul 27, 2022

fkp7057 

Use below formula-

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

 

 

fkp7057's avatar
fkp7057
Copper Contributor
Jul 27, 2022
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
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 27, 2022

    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)
)

Resources