CODEWITHSUNDEEP
excelvbaexcel-formula
skroutela
skroutela

Reputation: 125

Finding Highest Number from string in cell through excel formula or VBA

How to find the highest number from below string(Below string is in one cell only) in excel through formula or VBA?

1. sanjay r
2.kaustubh r
3.abc def
4. pqr dj
5.ss dd
6.sdd yy
7.ii lll
8.www eee
9.ddd dd
10.dlieis lsie

so with the above example, I should get the Answer as 10.

I have written below formula to get last "." in string as last number will always be highest number and all numbers has "." after it

=FIND("@",SUBSTITUTE(B37,".","@",(LEN(B37)-LEN(SUBSTITUTE(B37,".","")))/LEN(".")))

and then i have written another formula to get number attached to last "."

=IF(ISNUMBER(NUMBERVALUE(LEFT(RIGHT(B37,LEN(B37)-C37+3),1)))=FALSE,LEFT(RIGHT(B37,LEN(B37)-C37+2),1),LEFT(RIGHT(B37,LEN(B37)-C37+3),2))

And This is working perfectly fine but when there will be only one record in cell e.g.

  1. sanjay r

then i am getting output as "1." instead of 1 only

i tried a lot but couldn't fix this

need help here.

Upvotes: 0

Views: 222

Answers (1)

bosco_yip
bosco_yip

Reputation: 3802

In B1, formula copied down :

=-LOOKUP(1,-MID(A1,IFERROR(1-LOOKUP(,-FIND(CHAR(10),A1,ROW(A:A))),1),ROW($1:$9)))

enter image description here

Upvotes: 1

Related Questions