# Extract Text for Sheet Reference

#### legalhustler

##### Well-known Member
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

Last edited:

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### tusharm

##### MrExcel MVP
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.
Code:
``=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
Code:
``="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

##### Well-known Member
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.

Last edited:

#### legalhustler

##### Well-known Member
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.
Code:
``=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
Code:
``="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?

Last edited:

#### legalhustler

##### Well-known Member
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.

Last edited:

#### yky

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
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

Replies
3
Views
554
Replies
25
Views
804
Replies
2
Views
159
Replies
3
Views
262
Replies
15
Views
589

1,195,910
Messages
6,012,257
Members
441,687
Latest member
urimagic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back