manipulating text strings that are cell references

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a formula in a cell that creates a text string that represents a cell reference using the address function (i.e. the text string could be 'Sheet1'!D50). This text string varies depending on various inputs. I then want to extract the row and column number from this text string for use in a further formula. Is there anyway of doing this.

Any help would be greatly appreciated!
 
Assuming your formula is in A1 and it returns: Sheet1!D5

Depending on what you're after, try these:

In B1: =INDIRECT(A1)
(this returns the value in D5 of Sheet1)

In C1: =CELL("Row",INDIRECT(A1))
(this returns 5, ie the row # of "Sheet1!D5")

In D1: =CELL("Col",INDIRECT(A1))
(this returns 4, ie the col # of "Sheet1!D5")

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
Andrew said:
I have a formula in a cell that creates a text string that represents a
Click to expand...
cell reference using the address function (i.e. the text string could be
'Sheet1'!D50). This text string varies depending on various inputs. I then
want to extract the row and column number from this text string for use in a
further formula. Is there anyway of doing this.
 
Andrew,

Let me suppose that you have created the text string 'Sheet1'!D50 on cell
A1.

In cell B1, you can enter this formula and extract the column number ( 4 for
Column D)

=COLUMN(INDIRECT(MID(A1,FIND("!",A1)+1,255)))

If you substitute ROW for COLUMN in the above formula, you will get the the
row number - i.e. 50

Hope this helps you.

Regards,
Murthy
Andrew said:
I have a formula in a cell that creates a text string that represents a
Click to expand...
cell reference using the address function (i.e. the text string could be
'Sheet1'!D50). This text string varies depending on various inputs. I then
want to extract the row and column number from this text string for use in a
further formula. Is there anyway of doing this.
 
Back
Top