Split the row number from the link

[krishnakuma6]

Dear Experts

I want to split the row number from the linked cell (as attached in sheet 2, "B" column), how to get this using formula not macros

Rgds
Krishna

[LokeshKumar]

Hi,

I am not sure there are many genius will give you very short formula...till then use the following.
Its CSE (when entering this in cell press CTRL+Shift+Enter)

Please Login or Register  to view this content.

Please Click STAR to Add Reputation if my/someone's answer helped!

[Glenn Kennedy]

At the moment (but maybe the real data are not the same???) the row number is identical to the number in the reference, so =row() will give you the answer.

However, if that is just a coincidence; this regular formula will also do the job:

=LOOKUP(99^99,--(0&MID(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&1234567890)),ROW(INDIRECT("1:"&LEN(A3)+1)))))

or this:

=--RIGHT(A3,1+LEN(A3)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")))

[cbatrody]

Try the following formula in B3:

=1*SUBSTITUTE(RIGHT(A3,LEN(A3)-SEARCH("B",A3,1)),"$","")