Formula Errors

How to Add Text Before Every Third Row in Excel using a Formula

Kushani Nimanthika Oct 23, 2026 868 views

Question details

The user needs to add a specific text prefix to every third value in an Excel column automatically, without having to manually edit thousands of rows.

How to Add Text Before Every Third Row in Excel using a Formula

Product: Excel
Device & OS: not provided
Scenario: Modifying a large dataset by appending text prefixes to specific rows based on their sequence.
Observed behavior: The user requires an automated formula-based approach to conditionally append text to every third row while leaving other rows as their original values.

Before you start

Ensure your dataset is organized continuously in a single column, and note the starting worksheet row number to verify the formula calculates the third-row intervals correctly.

Solution 1Recommended

Use the IF and MOD Functions to Add a Prefix

This solution uses a combination of the IF, MOD, and ROW functions to logically determine the row number and conditionally append text to every third row.

The MOD function calculates the remainder of a division, while the ROW function returns the current row number. By combining them inside an IF statement, you can target specific row intervals, such as every third row, and append a text string to the original value.

Select the destination cell

Click on the first cell in an empty adjacent column (for example, cell G2) where you want the new modified values to appear.

Enter the logical formula

Type the formula `=IF(MOD(ROW(),3)=0, "SN: "&F2, F2)` into the formula bar. Replace 'F2' with your actual source cell and 'SN: ' with your desired prefix.

Apply the formula

Press Enter to apply the calculation to the first cell. You should see the prefix added if the row number is divisible by 3, or the original value if it is not.

Fill the formula down the column

Click and drag the fill handle (the small square at the bottom-right corner of the selected cell) down to copy the formula to the rest of the rows in your dataset.

Use the IF and MOD Functions to Add a Prefix

Row Number Dependency: This specific formula relies on the absolute worksheet row number. If your data starts on a different row (e.g., row 2) and you want the interval based strictly on the data range, you may need to adjust the ROW() function mathematically, such as using `MOD(ROW()-1, 3)`.

Efficient Data Formatting

Use WPS Spreadsheet to Quickly Add Prefixes to Rows

WPS Spreadsheet supports all standard logical and mathematical formulas, including IF, MOD, and ROW, allowing you to manipulate large datasets seamlessly. It is an excellent, lightweight alternative that handles complex data formatting tasks perfectly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the dataset you need to format.
2. Input the text-addition formula: Click on a target cell in a blank column and input `=IF(MOD(ROW(),3)=0, "Prefix: "&A1, A1)`.
3. Drag the fill handle: Select the cell, grab the small green square in the bottom right corner, and drag it down to apply the text addition to all required rows instantly.

Fully compatible with Microsoft Excel formulas and .xlsx files.Process and format thousands of rows of data without lag.Free, lightweight, and features a familiar user interface for immediate productivity.

microsoft office alternative - wps office

Frequently Asked Questions

How do I add text to every second row instead of every third?

Simply change the divisor in the MOD function from 3 to 2. Your modified formula will look like `=IF(MOD(ROW(),2)=0, "Prefix: "&F2, F2)`.

Can I add a suffix instead of a prefix?

Yes, you can append text at the end of the cell value by placing the text string after the cell reference in your formula. Use this structure: `=IF(MOD(ROW(),3)=0, F2&" Suffix", F2)`.

What if my data starts on row 2 and I want the 3rd data row to get the text?

You can adjust the ROW() function to account for the header by subtracting the starting rows. Use `=IF(MOD(ROW()-1,3)=0, "Prefix: "&F2, F2)` so the calculation aligns with your actual data range.

How can I replace the formulas with just the text values once I am done?

Select the entire column containing your new formula results, copy it (Ctrl+C), then right-click the destination column and choose 'Paste Special' > 'Values'. This removes the underlying formulas while keeping the newly formatted text.

Excel & Data Problems