How to Use VBA to Split Numbers into Fixed Columns
Question details
The user needs to split numerical values into individual digits across separate columns, ensuring the final digit aligns to a specific fixed column regardless of the number's total length.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Formatting and organizing variable-length numerical data where right-alignment of split digits into fixed column offsets is required.
- Observed behavior
- Numbers must be parsed digit-by-digit into adjacent cells, with shorter numbers receiving leading blank cells so that the last digit is placed in a consistent, fixed position.
Ensure that you have enabled Developer tools and macros in your spreadsheet application, and make sure to back up your data since macros cannot usually be undone.
Use a VBA Macro for Right-Aligned Digit Splitting
Run a custom VBA script that calculates each value's length and writes each digit into an offset, aligning the last digit perfectly to a fixed column.
This VBA procedure processes your selected data range, clears the designated output area to prevent overlapping data, and places digits into adjacent columns based on string length.
Sub SplitCells() Dim rng As Range, cel As Range Const m As Long = 17 Dim i As Long, n As Long, v As String Application.ScreenUpdating = False Set rng = Selection.Columns(1) rng.Offset(, 1).Resize(, m).ClearContents For Each cel In rng.Cells v = CStr(cel.Value) n = Len(v) For i = 1 To n cel.Offset(, m - n + i).Value = Mid(v, i, 1) Next i Next cel Application.ScreenUpdating = True End Sub
Press Alt + F11 in your spreadsheet to launch the VBA Editor. Go to Insert > Module to create a new blank module window.
Copy the provided `SplitCells` VBA code and paste it into the module. You can change the constant `m` (e.g., `Const m As Long = 17`) to adjust how many columns to the right the final digit should be placed.
Close the VBA Editor and return to your worksheet. Highlight only the cells containing the numbers you want to split. Do not include header rows (e.g., start selecting from A2 downwards).
Press Alt + F8 to open the Macro dialog box, select `SplitCells`, and click Run. The digits will now be split into individual columns with the last digit right-aligned.
Execute Macros Seamlessly in WPS Spreadsheet
WPS Office fully supports VBA macros, making it easy to automate complex data tasks like splitting numbers into fixed columns without extra configurations.
- 1. Install the software: Download and install the free version of WPS Office.
- 2. Open your spreadsheet: Launch WPS Spreadsheet and open the file containing the numbers to be split.
- 3. Access Developer Tools: Navigate to the Developer tab on the ribbon to open the VBA Editor.
- 4. Run the script: Paste the digit-splitting macro into a new module and run it on your selected data.
Frequently Asked Questions
How do I change which column the last digit appears in?
In the VBA code, modify the `Const m As Long = 17` line. The number represents the column offset from your currently selected column. Decrease the value to shift the output columns to the left, or increase it to shift them further to the right.
Why did the macro delete my adjacent data?
The provided VBA macro contains a `.ClearContents` command for the target output area to prevent old data from mixing with new results. Always ensure your destination columns to the right are empty or insert new blank columns before running the script.
Can I use this macro if my data has headers?
Yes, but you must select only the numerical data range (for example, starting at cell A2 downwards) before running the macro. Avoid selecting the header row so it doesn't get processed and split as text.
