Jul 27 2022 02:15 AM
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.
Jul 27 2022 02:33 AM
Jul 27 2022 03:14 AM
Jul 27 2022 03:39 AM
SolutionYou 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)
)
Jul 27 2022 03:39 AM
SolutionYou 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)
)