VBA & Macro Problems

Automatically Wrap Excel Text Before an Inventory or Street Number in WPS Office

Steven Parker Nov 13, 2026 869 views

Question details

The user needs a reusable VBA macro for WPS Spreadsheets/Excel that processes only column B, finds the first digit in each cell, and inserts a line break before it so street addresses or inventory numbers wrap inside the cell.

Automatically Wrap Excel Text Before an Inventory or Street Number in WPS Office

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: Monthly data processing involves hundreds of rows and seven columns; only column B must be formatted, with the break appearing at the street or inventory number in the same cell.
Observed behavior: Formatting is currently done manually and is tedious, so the user needs an automated, repeatable wrapping routine for monthly data.

Before you start

Save a backup copy of the workbook and confirm that the VBA editor opens in WPS Spreadsheets with Alt+F11. If you plan to reuse the macro each month, save the file as a macro-enabled workbook so the macro is retained.

Solution 1Recommended

Use a VBA Macro to Insert a Line Break Before the First Number in Column B

Use this when you need a repeatable, automated wrap for monthly WPS Spreadsheets or Excel data without manually editing hundreds of address or description cells.

The macro below targets column B only, turns on Wrap Text, removes existing line breaks, and inserts a line break before the first digit. Save the workbook as a macro-enabled file so the macro remains available next month.

Open the VBA editor

Open your workbook in WPS Spreadsheets, press Alt+F11, then choose Insert > Module in the Visual Basic Editor.

Paste the macro code

Paste the following macro into the module: Sub WrapBeforeFirstNumber() Dim cell As Range Dim i As Integer Dim txt As String For Each cell In Range("B1", Range("B" & Rows.Count).End(xlUp)) If cell.Value <> "" Then txt = cell.Value txt = Replace(txt, vbCrLf, " ") txt = Replace(txt, vbLf, " ") txt = Replace(txt, vbCr, " ") For i = 1 To Len(txt) If Mid(txt, i, 1) Like "#" Then txt = Left(txt, i - 1) & vbLf & Mid(txt, i) Exit For End If Next i cell.Value = txt cell.WrapText = True End If Next cell End Sub

Run the macro

Close the VBA editor and press Alt+F8. Select the macro name, such as WrapBeforeFirstNumber, and click Run.

Save as a macro-enabled workbook

After testing on a backup copy, save the file as a macro-enabled workbook (.xlsm) so the automated wrapping is available for future monthly data.

Use a VBA Macro to Insert a Line Break Before the First Number in Column B

Expected result: The macro wraps the cell text before the first number and turns on Wrap Text only for column B. If a cell contains no digit, it remains unchanged.

WPS Spreadsheets VBA Automation

Insert Automatic Line Breaks in WPS Spreadsheets with a Column-Specific VBA Macro

Create and run a VBA macro in WPS Spreadsheets to automatically break column B text before street or inventory numbers. The process works directly in WPS Office and saves the result in a macro-enabled Excel-compatible file.

1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the monthly Excel file that contains the address or inventory data.
2. Open the VBA editor: Press Alt+F11, then go to Insert > Module to create a new module.
3. Paste the column-B macro: Paste the WrapBeforeFirstNumber macro into the module and close the Visual Basic Editor.
4. Run and save the macro: Press Alt+F8, select the macro, click Run, confirm column B wraps correctly, and save the workbook as a macro-enabled file.

Automatically wraps only column B before the first digit in each cellRemoves existing line breaks to avoid stacked or messy wrapsWorks with Excel macro-enabled .xlsm files and familiar VBA codeFree to use in WPS Office with a familiar ribbon and shortcut workflowReusable monthly for hundreds of address or inventory rows

microsoft office alternative - wps office

Frequently Asked Questions

Why should I save the workbook as a macro-enabled file?

WPS Spreadsheets can run VBA macros, but ordinary .xlsx files do not retain macro code. Save as .xlsm to keep the macro available for the next monthly batch.

How do I target a different column instead of column B?

In the macro, replace the Range("B1", Range("B" & Rows.Count).End(xlUp)) part with the column letter you need, such as Range("C1", Range("C" & Rows.Count).End(xlUp)).

What does the macro do if a cell already contains line breaks?

It first replaces existing vbCrLf, vbLf, and vbCr characters with spaces, then inserts a single clean vbLf before the first digit, so old wraps do not create extra blank lines.

Will the macro break before every number in a cell?

No. The loop finds only the first digit and inserts one line break immediately before that position, leaving the remaining text and numbers together.

What if my WPS Spreadsheets macro doesn't run?

Confirm that you opened the VBA editor with Alt+F11, enabled macros for the workbook, and saved the file as .xlsm. Test the macro on a backup copy first.

Excel & Data Problems