# Thread: plz help.. which formula

1. ## plz help.. which formula

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  Reply With Quote

2. This could be a (very simple) solution:

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

3. Try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(B1)",B1),"(C1)",C1),"(D1)",D1)  Reply With Quote

4. 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  Reply With Quote

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

thanks  Reply With Quote

6. Mine was somewhat silly but Bob's formula works (tested it).  Reply With Quote

7. ## 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)  Reply With Quote

8. Bob, yes it worked.. thx alot  Reply With Quote

formulas 