CONCAT function

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
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.


Excel 2016 (Windows) 32 bit
BCDEFGHIJ
53OAS 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
Code:
=IF(personal_info!C9=0,"Name",personal_info!C9)
Any and all help is much appreciated. Cheers!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Re: Need help with CONCAT function

Glad you sorted it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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
Back
Top