plz help.. which formula

krizmail

New member
Joined
Feb 15, 2014
Messages
4
Reaction score
0
Points
0
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
Joined
Feb 22, 2013
Messages
82
Reaction score
0
Points
6
Location
Cordenons
Excel Version(s)
2016 32bit
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
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Try

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

krizmail

New member
Joined
Feb 15, 2014
Messages
4
Reaction score
0
Points
0
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
Joined
Feb 15, 2014
Messages
4
Reaction score
0
Points
0
@bob: i've tried your suggestion but didn't work.. hopefully u have other suggestions

thanks
 

rollis13

Member
Joined
Feb 22, 2013
Messages
82
Reaction score
0
Points
6
Location
Cordenons
Excel Version(s)
2016 32bit
Mine was somewhat silly ;) but Bob's formula works (tested it).
 
Last edited:

alderman

New member
Joined
Jan 23, 2014
Messages
19
Reaction score
0
Points
0
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)
 
Top