Formula Errors

How to Use IF and ISBLANK to Conditionally Concatenate Text in Excel

Khadija Khan Oct 13, 2026 872 views

Question details

The user needs to construct a dynamic concatenation formula that conditionally includes specific text (such as a cancellation notice) only if a target cell contains data.

How to Use IF and ISBLANK to Conditionally Concatenate Text in Excel

Product: Excel
Device & OS: not provided
Scenario: Building a dynamic text string from multiple cells where certain segments should be omitted entirely if the corresponding reference cells are blank.
Observed behavior: Without conditional checks, concatenating empty date or currency cells often results in awkward blank spaces, static text fragments that don't make sense, or default placeholder dates like '01/00/1900'.

Before you start

Identify the target cell that will trigger your conditional text. Verify that your date and currency cells contain valid numerical values so they can be correctly formatted within the text string.

Solution 1Recommended

Use the IF Function with an Empty String Check

This is the most robust method, as checking for an empty string handles both completely blank cells and cells containing formulas that return blanks.

While ISBLANK is useful, it evaluates to FALSE if a cell contains a hidden formula. Using the empty string logical test (= "") ensures your conditional string behaves correctly regardless of whether the cell is truly blank or just appearing empty due to another formula.

Construct the base concatenation string

Start your formula with the equals sign (=) and combine your static text (enclosed in quotation marks) with your main cell references using the ampersand (&) operator. For example: ="Pet's Name: " & AC2

Format initial date and currency values

When pulling in dates, wrap the cell reference in the TEXT function to prevent it from displaying as a serial number. Example: & ". Policy effective " & TEXT(AD2, "mm/dd/yyyy")

Insert the IF function for the conditional text

At the point where the conditional sentence should appear, append the IF function: & IF(

Configure the empty string check and format conditional values

Set the logical test to check for an empty string (e.g., AG2=""). For the TRUE argument, output an empty string (""). For the FALSE argument, construct your conditional text, nesting TEXT functions as needed. Example: AG2="", "", " Policy was cancelled on " & TEXT(AG2, "mm/dd/yyyy") & ".")

Complete the formula and press Enter

Combine all parts into one continuous formula and press Enter. The conditional sentence will now be entirely omitted if the cell AG2 is blank or contains an empty string.

Use the IF Function with an Empty String Check

Pro Tip: A complete formula utilizing this method looks like: ="Pet's Name: "&AC2&". Policy effective "&TEXT(AD2,"mm/dd/yyyy")&" thru "&TEXT(AE2,"mm/dd/yyyy")&"."&IF(AG2="",""," Policy cancelled on "&TEXT(AG2,"mm/dd/yyyy")&".")

Solution 2

Use the ISBLANK Function in Concatenation

Use this method if you are certain your referenced cells are completely empty and do not contain hidden formatting or empty string formulas.

Advanced Spreadsheet Software

Easily Manage Complex Formulas with WPS Spreadsheet

WPS Spreadsheet provides powerful support for logical functions like IF and ISBLANK, alongside advanced text manipulation tools. Build, test, and troubleshoot complex nested formulas with ease.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing the data you wish to concatenate.
2. Initiate the formula: Select an empty cell where you want the combined text to appear and type the equals sign (=).
3. Combine static text and cell references: Use the ampersand (&) operator to link text strings in quotation marks with your targeted data cells.
4. Integrate the IF logic: Use WPS Spreadsheet's intuitive formula prompts to add the IF and TEXT functions for your conditional formatting.
5. Apply the result: Press Enter to calculate the cell and drag the fill handle down to apply the dynamic concatenation formula to the rest of your column.

Fully compatible with Microsoft Excel formulas and functionsColor-coded syntax highlighting makes reading complex nested formulas easierBuilt-in function dialog for step-by-step formula configuration and troubleshootingFree to download and use for your daily personal and professional spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my concatenated blank date show as 01/00/1900?

Spreadsheet software stores dates as sequential serial numbers starting from January 1, 1900. When a cell is blank, it evaluates to 0, which corresponds to January 0, 1900. Using an IF condition to check for blanks prevents this placeholder date from appearing in your text.

What is the difference between using ISBLANK() and ="" in an IF statement?

The ISBLANK() function only returns TRUE if a cell is entirely devoid of any content. If a cell contains a formula that results in an empty string, ISBLANK() will evaluate to FALSE. Using ="" safely handles both truly empty cells and formula-generated blank strings.

How do I format a concatenated number as currency?

You can wrap the cell reference in the TEXT function with a specific currency format code. For example, using TEXT(AH2, "$#,##0.00") within your concatenation formula will properly format the raw number as a standard currency string.

Can I use the CONCAT or CONCATENATE function instead of the ampersand (&)?

Yes, both the CONCAT and CONCATENATE functions can be used alongside the IF function to achieve the same result. However, using the ampersand (&) is often preferred for dynamic strings because it keeps the formula shorter and easier to read when mixing multiple static strings and references.

Excel & Data Problems