# plz help.. which formula

#### krizmail

##### New member
hi.. i'm working with alot of data i must input but i dont know what formula i can use.. here's the sample case:

A1: The (B1) box in room number (C1) will be sent to (D1)

B1: red
C1: 6
D1: US

i need to have the result on F1: The red box in room number 6 will be sent to US

what formula should i put in F1?

thanks alot

#### rollis13

##### Member
This could be a (very simple) solution:

=LEFT(A1,4) & B1 & MID(A1,9,20) & C1 & MID(A1,33,17) & D1

#### Bob Phillips

##### Super Moderator
Staff member
Try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(B1)",B1),"(C1)",C1),"(D1)",D1)

#### krizmail

##### New member
seems that it will work if the text in A1 have the same number of charctr.. in my case text can be vary, so i need a formula that if text in A1 contain "(B1)" it will be replaced with text in B1 itself..

thanks

#### krizmail

##### New member
@bob: i've tried your suggestion but didn't work.. hopefully u have other suggestions

thanks

#### rollis13

##### Member
Mine was somewhat silly but Bob's formula works (tested it).

Last edited:

#### alderman

##### New member
CONCATENATE will work as well

Id the sentence never changes then the CONCATENATE equation is simple...

=CONCATENATE("The ",B1," box in room number ", C4," will be sent to ",A5)

#### krizmail

##### New member
Bob, yes it worked.. thx alot