Extract Text for Sheet Reference

[legalhustler]

I would like to extract the letter after the first dollar sign in cell A10, then use it as a sheet reference. My formula below is not working correctly, can someone help fix it.

A10 contains:

Cell($D$35)

My Formula [not working properly]:

=Sheet1!&"$"&left(SUBSTITUTE(MID(LEFT(A10,LEN(A10)-1),FIND("(",A10)+1,99),"$",""),1)&"$"&3):$D$10


The above formula should result in:

Sheet1!$D$3:$D$10

 

[tusharm]

Lightly tested:

The below extracts the letter into a cell. So, put it in an intermediate cell. You can then use that intermediate cell to complete the formula in the desired cell.

=MID(A10,FIND("$",A10)+1,FIND("$",A10,FIND("$",A10)+1)-(FIND("$",A10)+1))

So, if the above formula is in C10 then to get the desired result in the final cell, use the formula

="Sheet1!$"&C10&"$3:$"&C10&"$10"

I would like to extract the letter after the first dollar sign in cell A10, then use it as a sheet reference. My formula below is not working correctly, can someone help fix it.

A10 contains:

Cell($D$35)

My Formula [not working properly]:

=Sheet1!&"$"&left(SUBSTITUTE(MID(LEFT(A10,LEN(A10)-1),FIND("(",A10)+1,99),"$",""),1)&"$"&3):$D$10


The above formula should result in:

Sheet1!$D$3:$D$10

 

[yky]

So, you want to extract "D". Is that right? Are there always two dollar signs, before and after the desired letters? If so, try this:

=TRIM(MID(SUBSTITUTE(A10,"$", REPT(" ",50)),50,20))

This formula extracts "D" from A10. Wrap it with proper strings to get what you want.

 

[legalhustler]

Lightly tested:

The below extracts the letter into a cell. So, put it in an intermediate cell. You can then use that intermediate cell to complete the formula in the desired cell.

=MID(A10,FIND("$",A10)+1,FIND("$",A10,FIND("$",A10)+1)-(FIND("$",A10)+1))

So, if the above formula is in C10 then to get the desired result in the final cell, use the formula

="Sheet1!$"&C10&"$3:$"&C10&"$10"

I actually needed one combined formula. I combined your formula (see below) and it resulted in:

Cell A10 contains:

Cell($D$35)

="Sheet1!$"&MID(A10,FIND("$",A10)+1,FIND("$",A10,FIND("$",A10)+1)-(FIND("$",A10)+1))&"$3:$"&MID(A10,FIND("$",A10)+1,FIND("$",A10,FIND("$",A10)+1)-(FIND("$",A10)+1))&"$10"

Formula in a empty cell results into: Sheet1!$D$3:$D$10

The result above is exactly what I wanted when I put it in an empty cell, but when I put the formula in the lookup_array argument of the MATCH function and evaluate the formula it puts double quotes i.e. "Sheet1!$D$3:$D$10"

How can I make the reference without the double quotes?

 

[legalhustler]

ADVERTISEMENT

So, you want to extract "D". Is that right? Are there always two dollar signs, before and after the desired letters? If so, try this:

=TRIM(MID(SUBSTITUTE(A10,"$", REPT(" ",50)),50,20))

This formula extracts "D" from A10. Wrap it with proper strings to get what you want.

Yes the cells always has two $ signs. I have already extracted the letter "D" in my original formula - that's not the issue though. The issue now using the combined formula from tusharm is that it puts double quotes when I use it in the lookup_array argument of the MATCH function. I need to have reference show without double quotes.

 

[yky]

The result above is exactly what I wanted when I put it in an empty cell, but when I put the formula in the lookup_array argument of the MATCH function and evaluate the formula it puts double quotes i.e. "Sheet1!$D$3:$D$10"

Could you post the MATCH function?

 

[tusharm]

ADVERTISEMENT

Wrap the part of the formula that gives the sheet+range reference inside the INDIRECT function.

Do keep in mind that creating a single giant formula will make it difficult to debug, understand, or maintain, not to mention a potential performance drag. Using "helper" cells is often a smarter way to go.

I actually needed one combined formula. I combined your formula (see below) and it resulted in:

Cell A10 contains:

Cell($D$35)

="Sheet1!$"&MID(A10,FIND("$",A10)+1,FIND("$",A10,FIND("$",A10)+1)-(FIND("$",A10)+1))&"$3:$"&MID(A10,FIND("$",A10)+1,FIND("$",A10,FIND("$",A10)+1)-(FIND("$",A10)+1))&"$10"

Formula in a empty cell results into: Sheet1!$D$3:$D$10

The result above is exactly what I wanted when I put it in an empty cell, but when I put the formula in the lookup_array argument of the MATCH function and evaluate the formula it puts double quotes i.e. "Sheet1!$D$3:$D$10"

How can I make the reference without the double quotes?

 

[legalhustler]

Wrap the part of the formula that gives the sheet+range reference inside the INDIRECT function.

Do keep in mind that creating a single giant formula will make it difficult to debug, understand, or maintain, not to mention a potential performance drag. Using "helper" cells is often a smarter way to go.

BINGO!! Thanks, exactly what I was looking for. Spreadsheet real estate is expensive for client can't afford extra helper cells :P

 

[tusharm]

You are welcome. And, yes, I understand about real estate. After all, the client has only 17,179,869,184 cells in a worksheet.

BINGO!! Thanks, exactly what I was looking for. Spreadsheet real estate is expensive for client can't afford extra helper cells :P