CONCAT function

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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.

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0 " /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0 ;text-align: center;color: #201116"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">53</td><td style="font-weight: bold;border-left: 1px solid black;font-style: italic;;">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.</td><td style="font-weight: bold;font-style: italic;;"></td><td style="font-weight: bold;font-style: italic;;"></td><td style="font-weight: bold;font-style: italic;;"></td><td style="font-weight: bold;font-style: italic;;"></td><td style="font-weight: bold;font-style: italic;;"></td><td style="font-weight: bold;font-style: italic;;"></td><td style="font-weight: bold;font-style: italic;;"></td><td style="font-weight: bold;border-right: 1px solid black;font-style: italic;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0 ;color: #201116">cpp&oas</p><br /><br />The formula that I want to add to replace the two occurrences of the word "Dale" is as follows
Code:
=IF(personal_info!C9=0,"Name",personal_info!C9)
Any and all help is much appreciated. Cheers!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
Re: Need help with CONCAT function

Try this

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

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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.

Code:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Re: Need help with CONCAT function

Glad you sorted it & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,123,264
Messages
5,600,599
Members
414,393
Latest member
Vignesh Mechz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top