How do i find the row number from a formula

PeterMac2020 · Sep 3rd 2020

Hi,

I have a formula in Cell A1

=BOQ!N528

In cell B1 i need the value 528

I would like a formula to extract the row number from the formula in cell A1

Hope someone can help

Regards Peter

PeterMac2020 · Sep 3rd 2020

I came across a somple function to show the formula as string

Code

=ShowF(A1) returns "=BOQ!N528" so i just need to split the string between N and 5 and convert back to a number please

PeterMac2020 · Sep 3rd 2020

I was over thinking it.

Settled for

=VALUE(MID(showf(A1),7,4))

since i know the mid point

Regards Peter

**royUK** · Sep 3rd 2020

Welcome to Ozgrid. Please read the Forum Rules, I have added code tags for you this time.

You don't need VBA to show the formula you can use the

=FORMULATEXT(A1)

PeterMac2020 · Sep 3rd 2020

Thanks Roy, works well.

**royUK** · Sep 4th 2020

Pleased to help

Share