# how to combine text+value in formula please

#### slimimi

##### Well-known Member
Hi there
Anyone know how to do the following please:

Example,

I want the user to select a currency pair in cell B4, eg, EUR/USD

Then cell R4 will look to see if there is any data in B4 and, if so, return the following value into Cell R4....

=MT4|BID! & cell B4 & "m"

the cell b4 is the bit i am not sure about.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### IvenBach

##### Board Regular
=IF(MT4|BID!B4<>"",MT4|BID!B4&"m","")

I think this is what you are looking for. I can be wrong.

#### slimimi

##### Well-known Member
Hi iven - thanks for your help. Its almost there in terms of what i am looking for - would you mind helping me to tweak it please?

I will be more specific with my actual spreadsheet cells to make things easier.

In C4 : user will enter a currency pair, ie, EUR/USD (i need to figure out how to get rid of the / symbol too)

So in BP4 i would like it to grab the relevant price data via DDE for the correct currency pair entered in C4.

the formula i am using to grab this data is : =MT4|BID!EURUSDm : this works fine

so this formula needs to change the EURUSD bit to whatever currency pair (minus the / symbol) is selected in C4. Hope this makes sense so far.

I wanted to put an IF statement too in BP4 so that if nothing is entered in C4 then BP4 should just remain blank. i guess something like a =if(C4>0,etc

thanks in advance if you can help...

#### schielrn

##### Well-known Member
Maybe try:

=if(c4="","",indirect("MT4|BID!" & substitute(c4,"/","") & "m"))

Hope that helps.

#### slimimi

##### Well-known Member

Maybe try:

=if(c4="","",indirect("MT4|BID!" & substitute(c4,"/","") & "m"))

Hope that helps.
hi thanks alot for your help. I learnt about substitute from you and its a great feature.
However - i cant get the whole formula to work. I keep getting a ref error/

I went to Cell A1 and typed in EUR/USD
then i pasted your formula into B1 and changed the C4's to A1s

Would you be able to help further on this please?

#### schielrn

##### Well-known Member
Ok what result do you get with the formula:

="MT4|BID!" & substitute(A1,"/","") & "m"

In your example I get

<TABLE style="WIDTH: 153pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=204 border=0 x:str><COLGROUP><COL style="WIDTH: 153pt; mso-width-source: userset; mso-width-alt: 7460" width=204><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 153pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=204 height=17>MT4|BID!EURUSDm</TD></TR></TBODY></TABLE>

Is that the exact name of the sheet?

#### slimimi

##### Well-known Member

Yes you are right - thats what i am getting now but the problem is that it is appearing as text in a cell. I am not referencing to any sheet but to an Application called MT4.exe

For example, if i type in the following into a cell :

=MT4|BID!EURUSDm

this will give me the current Bid Price for the Euro against the US dollar and constantly changes via DDE

The problem is the part i have highlighted in red will change depending on what currency pair the user selects from Cell A1

Hope that helps.
Thanks a million in advance

#### schielrn

##### Well-known Member
Is EURUSDm a named range on the sheet MT4|BID?

I'm not sure why the indirect function would not be providing an answer?

Last edited:

#### slimimi

##### Well-known Member
If it helps - i have used data validation in a cell whereby the user selects from a drop down list of currency pairs. Its not a named range (i dont think so anyway).

They basically just select from 10 currency pairs.

What you have taught me so far works perfect with what text it places in the cell but i just need excel to see that as a formula, like, =MT4|BID!EURUSDm instead of just showing the text "MT4|BID!EURUSDm" in the cell.

Hope that makes sense.

#### slimimi

##### Well-known Member
i did try adding "=MT4, etc" instead but that didnt do the trick

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,990
Messages
5,834,751
Members
430,315
Latest member
bobh63

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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