Inserting value from cell in another cell within quotes

[analystintraining]

Hi all, I have a question that may be very basic, but I'm stumped. I have a list of thousands of values (CUSIP data), and I need the information in these cells for another formula. For example, cell A2 could be 99934U0F2, and I need that information to go into another formula in cell B2: =BDP("99934U0F2 CUSIP, "CNTRY_OF_DOMICILE").

Now I can copy-paste the value from A2 inside the cell and then paste it into the formula in B2, but I have thousands of these that I need to run. My issue is inserting the value of another cell within quotations marks, since the second =BDP formula has to have the quotes.

Any suggestions and help are very welcome. Thank you!

 

[Toadstool]

I don't have the Bloomberg Addin but to insert double quotes in a string you can wrap double quotes in double quotes so something like this in B2

 =""""&A2&" CUSIP "&",CNTRY_OF_DOMICILE"""

will give cell B2 results of
"99934U0F2 CUSIP ,CNTRY_OF_DOMICILE"

 

[analystintraining]

Thank you so much for your help! I'm trying to get a result of "99934U0F2 CUSIP" ,"CNTRY_OF_DOMICILE", but adding those two extra quotations marks is proving to be tricky.

 

[Toadstool]

This should do it if A2 has 99934U0F2

=""""&A2&" CUSIP"""&" ,""CNTRY_OF_DOMICILE"""

 

[analystintraining]

Thank you, just what I was looking for!

 

[analystintraining]

I just realized I'm showing up with an extra quote and no comma: "99373U9F3 "CUSIP ""CNTRY_OF_DOMICILE"".

I don't need a quote before CUSIP, but I do need a comma between CUSIP and CNTRY_OF_DOMICILE, so that I get something like

=BDP("99934UOF2 CUSIP", "CNTRY_OF_DOMICILE"). Trying to take out commas around CUSIP just leaves me with errors, as well as trying to add a comma in between. Adding commas to the end of the string leaves me with too many.

 

[Toadstool]

Maybe take out the space before the comma?

	A	B
99934U0F2	"99934U0F2 CUSIP","CNTRY_OF_DOMICILE"

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=""""&A2&" CUSIP"""&",""CNTRY_OF_DOMICILE"""[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]