How to Add a Prefix to Every Line in Excel Cells Using VBA in WPS Spreadsheets
Question details
The user needs a VBA macro to automatically add a prefix such as '- ' or '* ' to every nonblank line in selected multiline cells in WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is processing a large worksheet with thousands of rows and wants to prefix every nonblank line in cells that contain multiline text.
- Observed behavior
- After running the macro, every nonblank line in the selected cells should start with the chosen prefix while blank lines remain unchanged.
Open the workbook in WPS Spreadsheets, make sure you can open the VBA editor with Alt+F11, and select the cells you want to process before running the macro.
Run a VBA Macro to Prefix Every Nonblank Line in Selected WPS Spreadsheets Cells
Use this solution when you need to batch-update hundreds or thousands of multiline cells without editing each line manually.
This method splits each cell value by line breaks, checks every line, and adds a prefix only when the line is not empty. It then rejoins the lines with the same line breaks and writes the updated text back to the cell.
In WPS Spreadsheets, select the range of cells that contains the multiline text you want to update.
Press Alt+F11 to open the Visual Basic for Applications editor.
In the Project pane, right-click the current workbook, click Insert, and then click Module.
Paste the following macro into the module: Sub AddAsterisk() Dim rng As Range Dim s() As String Dim i As Long Application.ScreenUpdating = False For Each rng In Selection s = Split(rng.Value, vbLf) If UBound(s) >= 0 Then For i = 0 To UBound(s) If s(i) <> "" Then s(i) = "- " & s(i) End If Next i rng.Value = Join(s, vbLf) End If Next rng Application.ScreenUpdating = True End Sub
Click Developer > Macros or press Alt+F8, select AddAsterisk, and click Run.
Click a processed cell and inspect the in-cell text or formula bar to confirm each nonblank line now begins with '- '.
Add a Prefix to Every Line in WPS Spreadsheets with a VBA Macro
Use the built-in VBA editor in WPS Spreadsheets to run a macro that processes all selected multiline cells at once, so every nonblank line gets the required prefix.
- 1. Select the target range: Open the workbook in WPS Spreadsheets and select the cells that contain multiline text.
- 2. Open the VBA editor: Press Alt+F11 to launch the Visual Basic for Applications editor.
- 3. Insert and paste the macro: Right-click the workbook in the Project pane, choose Insert > Module, and paste the prefix macro code.
- 4. Run the macro: Press Alt+F8, select AddAsterisk in the macro list, and click Run.
- 5. Check the result: Inspect the selected cells to confirm each nonblank line now starts with the chosen prefix.
Frequently Asked Questions
Can I use an asterisk instead of a hyphen as the prefix?
Yes. In the macro code, change "- " to "* " in the line s(i) = "- " & s(i), then run the macro again.
Will this macro add a prefix to empty lines inside a cell?
No. The condition If s(i) <> "" checks each line and skips empty strings, so only nonblank lines are updated.
Does this VBA code work in WPS Spreadsheets?
Yes. WPS Spreadsheets includes a VBA editor, so you can open it with Alt+F11 and run the macro the same way as in Excel.
How do I run the macro after pasting it into a module?
Press Alt+F8 to open the Macro dialog, select AddAsterisk, and click Run.
