Replace a Text Prefix with a Cell Value in WPS Office Spreadsheets
Question details
The user needs to replace a text prefix such as BR with a branch number from another cell while keeping the numeric suffix, and optionally keep the original prefix when the branch cell is blank.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Building or cleaning worksheet codes by substituting a fixed text prefix for a branch number that is stored in a separate cell, without manually editing every row.
- Observed behavior
- The worksheet contains codes like BR0004216, and the goal is to produce a formula-driven version that uses the branch number from H3 while preserving the numeric suffix.
Open the worksheet in WPS Office Spreadsheets and note which column holds the full code, which cell holds the branch number, and the fixed numeric suffix. Ensure the branch number cell is formatted as Text or General if leading zeros or exact display matter.
Replace the Prefix with a Cell Value Using SUBSTITUTE
Use this when the full code already exists in a cell and you want to replace only the BR prefix with the branch number from H3 while preserving the rest of the text.
The SUBSTITUTE function replaces a specified text string inside a cell. In this example, it replaces the literal text "BR" in C3 with the value from H3 converted to text. If your prefix is different, replace "BR" with the actual prefix in your workbook.
Click the first empty cell in a separate column next to your code column, for example D3.
Type =SUBSTITUTE(C3,"BR",H3&"") into D3 and press Enter. This replaces the BR prefix in C3 with the branch number from H3.
Drag the fill handle at the lower-right corner of D3 down through all rows to apply the formula to the remaining codes.
Concatenate a Branch Number with a Fixed Numeric Suffix
Use this when the numeric suffix is always the same and you only need to join the branch number from H3 with that fixed suffix.
Keep the Original BR Prefix When the Branch Cell Is Blank
Use this when you want the completed code to fall back to the original BR prefix if no branch number has been entered in H3.
Use WPS Office Spreadsheets to Replace Prefix Codes with Cell Values
Open your workbook in WPS Office Spreadsheets and use the same SUBSTITUTE and IF formulas to replace a prefix such as BR with a branch number from another cell while keeping the numeric suffix.
- 1. Download and open WPS Office: Download WPS Office from the official site, install it, and open your existing .xlsx workbook in WPS Office Spreadsheets.
- 2. Select the helper column: Click the first empty cell next to your code column, for example D3.
- 3. Enter the replacement formula: Type =SUBSTITUTE(C3,"BR",H3&"") and press Enter, or use =IF(H3,H3&"0004216","BR0004216") to keep the BR prefix when H3 is blank.
- 4. Fill the formula down: Drag the fill handle from the lower-right corner of D3 down through all rows to update every branch code.
Frequently Asked Questions
Can I replace the BR prefix without retyping each cell in WPS Office Spreadsheets?
Yes. Use =SUBSTITUTE(C3,"BR",H3&"") in an empty column and drag the fill handle down so the formula replaces BR with the branch number from H3 in every row.
How do I keep the original BR prefix when the branch number cell is empty?
Use =IF(H3,H3&"0004216","BR0004216") in the result cell. For a stricter blank check, use =IF(H3="","BR0004216",H3&"0004216").
What is the difference between SUBSTITUTE and simple concatenation for this task?
SUBSTITUTE replaces the existing BR prefix inside the original code, leaving the rest untouched. Concatenation builds the code from separate pieces, which works best when the suffix is fixed.
Will these formulas work in WPS Office with .xlsx files?
Yes. WPS Office Spreadsheets supports the standard Excel formula syntax and opens .xlsx workbooks without converting them, so your SUBSTITUTE and IF formulas work the same way.
Do I need to change the formula if my prefix is not BR?
Yes. Replace "BR" in the formula with your actual prefix, and update C3 and H3 to match the worksheet cells that contain the original code and the branch number.
