Substitute to include within a formula

[RAJESH1960]

Hello Experts,

I am trying to add substitute formula in the formula in column K. I think the placement is wrong or something. Can anyone tell me where am I going wrong? I should get the same names in column K as in column J with the exception that where there is & It should be replaced with &. I need the right formula in K2 so that I can drag it down to the last row. If I drag it down to 2000 rows. it should show blank if the cell A2 is blank.

4. Import Bank Excel 2019 11.04.2021.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1			Bank
2
3	02-03-2021		ABC & Co.	Payment	236.00				Bank	ABC & Co.	#VALUE!
4	02-03-2021		Sunday & Monday	Contra	5000.00				Bank	Sunday & Monday	#VALUE!
5	02-03-2021		Suspense	Payment	40000.00				Bank	Suspense	#VALUE!
6	02-03-2021		ABC & Co.	Receipt		25000.00			Bank	ABC & Co.	#VALUE!
7	02-03-2021		Suspense	Contra		4000.00			Suspense	Bank	FALSE
8	02-03-2021		Sunday & Monday	Payment	3949.00				Bank	Sunday & Monday	#VALUE!
9	02-03-2021		Suspense	Receipt		22147.00			Bank	Suspense	#VALUE!
10	01-03-2021		ABC & Co.	Receipt		17000.00			Bank	ABC & Co.	#VALUE!
11	01-03-2021		Suspense	Receipt		15910.00			Bank	Suspense	#VALUE!
12	01-03-2021		Suspense	Receipt		6000.00			Bank	Suspense	#VALUE!
13	01-03-2021		Suspense	Receipt		50000.00			Bank	Suspense	#VALUE!
14	01-03-2021		Sunday & Monday	Contra	50000.00				Bank	Sunday & Monday	#VALUE!
15	01-03-2021		ABC & Co.	Payment	36338.00				Bank	ABC & Co.	#VALUE!
16	01-03-2021		Suspense	Contra		40000.00			Suspense	Bank	FALSE
17	01-03-2021		Suspense	Contra	3000.00				Bank	Suspense	#VALUE!
18	01-03-2021		ABC & Co.	Receipt		13295.00			Bank	ABC & Co.	#VALUE!
Sheet1

Cell Formulas
Range	Formula
J3:J18	J3	=IF(I3=$C$1,C3,$C$1)
K3:K18	K3	=IF(I3=$C$1,SUBSTITUTE(C3,"&","&",$C$1))

 

[Fluff]

You have the closing bracket in the wrong place, it should be

=IF(I3=$C$1,SUBSTITUTE(C3,"&","&"),$C$1)

 

[jtakw]

Hi,

You have a misplaced bracket:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1			Bank
2
3	3/2/2021		ABC & Co.	Payment	236				Bank	ABC & Co.	ABC & Co.
4	3/2/2021		Sunday & Monday	Contra	5000				Bank	Sunday & Monday	Sunday & Monday
5	3/2/2021		Suspense	Payment	40000				Bank	Suspense	Suspense
6	3/2/2021		ABC & Co.	Receipt		25000			Bank	ABC & Co.	ABC & Co.
7	3/2/2021		Suspense	Contra		4000			Suspense	Bank	Bank
8	3/2/2021		Sunday & Monday	Payment	3949				Bank	Sunday & Monday	Sunday & Monday
9	3/2/2021		Suspense	Receipt		22147			Bank	Suspense	Suspense
10	3/1/2021		ABC & Co.	Receipt		17000			Bank	ABC & Co.	ABC & Co.
11	3/1/2021		Suspense	Receipt		15910			Bank	Suspense	Suspense
12	3/1/2021		Suspense	Receipt		6000			Bank	Suspense	Suspense
13	3/1/2021		Suspense	Receipt		50000			Bank	Suspense	Suspense
14	3/1/2021		Sunday & Monday	Contra	50000				Bank	Sunday & Monday	Sunday & Monday
15	3/1/2021		ABC & Co.	Payment	36338				Bank	ABC & Co.	ABC & Co.
16	3/1/2021		Suspense	Contra		40000			Suspense	Bank	Bank
17	3/1/2021		Suspense	Contra	3000				Bank	Suspense	Suspense
18	3/1/2021		ABC & Co.	Receipt		13295			Bank	ABC & Co.	ABC & Co.
Sheet910

Cell Formulas
Range	Formula
J3:J18	J3	=IF(I3=$C$1,C3,$C$1)
K3:K18	K3	=IF(I3=$C$1,SUBSTITUTE(C3,"&","&"),$C$1)

 

[RAJESH1960]

You have the closing bracket in the wrong place, it should be

=IF(I3=$C$1,SUBSTITUTE(C3,"&","&"),$C$1)

Got it. Thanks Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.