manipulating text strings that are cell references

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!
 
M

Max

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.
 
M

Murthy

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

manipulating text strings that are cell references 1
Cut the last 20 characters out of a text string 4
Find text based on multiple criteria 0
=IF function, reference problem to "text strings" in Data Validati 2
isolate date from a text string into another cell 5
Formula for extracting text formated numbers within ( )'s as numbe 5
return cell address of longest text string in a range 2
Formula to return a value for a portion of a text string in a cell 2

Top