Replace 0.00 with 1 in an Excel Text Value in WPS Spreadsheets
Question details
User needs to replace the 0.00 prefix in text values with 1 while keeping the remaining digits.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has text values that begin with 0.00 followed by a space and a number, and needs to change only the 0.00 prefix to 1.
- Observed behavior
- The existing values start with 0.00 and leave the remaining digits after the space unchanged; a formula is needed instead of manual editing.
Confirm whether your values always start with exactly 0.00 followed by a space, and locate an empty adjacent column where you can safely enter formula results.
Use TEXTAFTER to Replace the 0.00 Prefix with 1 While Keeping the Remaining Text
Best when your data always has a space after 0.00 and your version of WPS Spreadsheets supports the TEXTAFTER function.
TEXTAFTER extracts all text after the first space, so you only need to add the new "1" prefix. This avoids counting characters and works even when the remaining digits vary in length.
Click the cell where the cleaned value should appear, for example B1 next to the original value in A1.
Type ="1"&TEXTAFTER(A1," ") and press Enter. This returns a text string starting with 1 followed by everything after the first space.
Drag the fill handle from the bottom-right corner of B1 down to apply the formula to the remaining rows.
Copy the formula results, right-click the original range, and choose Paste Special > Values to replace the original text values.
Use REPLACE When Every Value Begins with Exactly 0.00
Use this fixed-position approach when the prefix is always exactly five characters and you want a simple formula that works in any version.
Replace 0.00 with 1 in WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible text functions such as TEXTAFTER and REPLACE, letting you transform text values directly in your workbook without any add-ins.
- 1. Open your workbook: In WPS Spreadsheets, open the file that contains the text values starting with 0.00.
- 2. Select an adjacent result cell: Click the first cell where you want the cleaned value, such as B1 next to A1.
- 3. Enter the appropriate formula: Type ="1"&TEXTAFTER(A1," ") or =REPLACE(A1,1,5,"1") depending on whether your data uses a space delimiter or a fixed 0.00 prefix.
- 4. Fill the formula down: Drag the fill handle down to apply the result to all affected rows.
- 5. Paste as values to finalize: Copy the result cells, right-click the target location, and select Paste Special > Values to replace the original text if needed.
Frequently Asked Questions
Which formula replaces the 0.00 prefix with 1 and keeps the rest of the text?
If your data has a space after 0.00 and your version supports TEXTAFTER, use ="1"&TEXTAFTER(A1," "). If the prefix is always exactly 0.00, use =REPLACE(A1,1,5,"1").
Does WPS Spreadsheets support the TEXTAFTER function?
Recent WPS Spreadsheets versions support Excel-compatible text functions including TEXTAFTER. If your version does not list TEXTAFTER, use the REPLACE formula for fixed 0.00 prefixes.
How do I replace the original values with the formula results?
Copy the formula results, right-click the destination area or original range, and choose Paste Special > Values. This removes the formulas and keeps the converted text.
Will the result be stored as text or number?
Using ="1"&TEXTAFTER(A1," ") returns a text string. If you need a numeric result, wrap the formula with VALUE, for example =VALUE("1"&TEXTAFTER(A1," ")).
What if the prefix is not always exactly 0.00?
If the text before the space can vary, use the TEXTAFTER formula because it removes everything before the space. If the pattern is different, adapt the function or use SUBSTITUTE/REPLACE based on the actual prefix.
