CONCAT function

[leopardhawk]

I would like to add a formula to this cell (B53) to replace the two occurrences of the word "Dale". The cell currently contains approximately 822 characters. Excel limits the text within a formula to 255 characters. I understand that the CONCAT functions allows for putting text strings together but I'm having issues with the syntax and keep getting errors.

Here is the cell as is, a single string of text.

Excel 2016 (Windows) 32 bit
	B	C	D	E	F	G	H	I	J
53	OAS monthly payments are reduced by $0.15 for every dollar your net income exceeds a defined threshold and will effectively be reduced to zero at some point. In some circles this is commonly referred to as the OAS Clawback but Canada Revenue Agency calls it the OAS Recovery Tax. OAS recovery tax is based on your previous year's net income and is applied from July of the current year to June of the following year as monthly deductions from your OAS payments. Example: Let's assume Dale's net income for the previous year exceeds the OAS minimum threshold by $17,420.00 making him subject to recovery tax in the amount of $2,613.00 ($17,420.00 x 15%). This amount is then divided by 12 and $217.75 is deducted from Dale's monthly OAS payments from July of the current year to June of the following year.
cpp&oas

The formula that I want to add to replace the two occurrences of the word "Dale" is as follows

=IF(personal_info!C9=0,"Name",personal_info!C9)

Any and all help is much appreciated. Cheers!

 

[theBardd]

Re: Need help with CONCAT function

Try this

=SUBSTITUTE(B53,"Dale",IF(personal_info!C9=0,"Name",personal_info!C9))

 

[leopardhawk]

Re: Need help with CONCAT function

My bad. I probably didn't explain things very well. I need all the text that is currently in cell B53 to stay there. Currently, the two occurrences of "Dale" are just part of the text. I want to replace them with a formula that will go to cell C9 on another worksheet called 'personal_info' and if C9 is blank, return 'Name' or if there is a name in C9, return the name that is in the cell.

I keep getting an error message to use CONCATENATE or ampersand (&) because I have so many characters (822) in cell B53 (maximum is 255 characters in a formula). This creates another set of errors for me as i attempt to concatenate the text strings together in no more than 255 characters.

This is my conundrum. Hope you can help!

 

[leopardhawk]

Re: Need help with CONCAT function

I am still hopeful that someone can help me with this problem that I am trying to solve. I'm fairly sure that it is a simple solution but I keep getting errors, likely because I have the syntax wrong.

 

[jimrward]

Re: Need help with CONCAT function

It would help if you could simplify your request as follows
Show the formula you are using also give a smaller sample of the original data along with what you would like that data to look like after applying the formula

 

[Fluff]

Re: Need help with CONCAT function

You can either use the formula that theBardd suggested, or you will need to split the text into various different cells & then concatenate them back together.

 

[leopardhawk]

Re: Need help with CONCAT function

Thanks. theBardd's formula gives a circular reference, I wanted to keep the formula within B53 to simplify the worksheet and for aesthetic reasons (I'm using a gradient background in the cell). I kept getting the 255 maximum character error when I tried to embed my IF statement into the CONCATENATE.

Anyway, I finally figured it out and the formula below works.

=CONCATENATE("OAS monthly payments are reduced by $0.15 for every dollar your net income exceeds a defined threshold and will effectively be reduced to zero at some point.
",
"In some circles this is commonly referred to as the OAS Clawback but Canada Revenue Agency calls it the OAS Recovery Tax.
",
"OAS recovery tax is based on your previous year's net income and is applied from July of the current year to June of the following year as monthly deductions from your OAS payments.
",
"Example: Let's assume "&IF(personal_info!C9=0,"Name",personal_info!C9)&"'s net income for the previous year exceeds the OAS minimum threshold by $17,420.00 making him subject to recovery tax in the amount of $2,613.00 ($17,420.00 x 15%).",
"                       This amount is then divided by 12 and $217.75 is deducted from "&IF(personal_info!C9=0,"Name",personal_info!C9)&"'s monthly OAS payments from July of the current year to June of the following year.")

Thanks to all for you advice.

Cheers!

 

[Fluff]

Re: Need help with CONCAT function

Glad you sorted it & thanks for the feedback