Forum Discussion
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.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.
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) )
- 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-cuYou 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) )
