Excel VBA Text to Columns for Fixed Width

VBA Breakdown

Set Rng = Application.InputBox(Prompt:="Select a range", Type:=8) 

• This line asks the user to choose a range of cells from the worksheet, then it assigns that selection to the variable “Rng“.

c = Int(InputBox("Enter the Number of Columns: ", "ExcelDemy")) 

• This line asks the user how many columns they want to divide the text into and put that value to the variable “c“.

Arr = InputBox("Enter the Numbers by Which You Want to Break the Columns. Separate by Commas.", "ExcelDemy") 

• This line asks the user to define the length of each column in a list of integers separated by commas and assign this value to the variable “Arr“.

Arr = Split(Arr, ",") 

• This line divides the user’s string of numbers into an array of distinct values using the Split function based on the comma.

Set Rng2 = Application.InputBox(Prompt:="Select a range", Type:=8) 

• In order to insert the split text in this range of cells, the user is prompted in this line, and the split text range is then assigned to the variable “Rng2“.

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Columns.Count
        Text =Rng.Cells(i, j)
        Start = 0

• These lines of code start a loop that repeatedly iterates through each row in the user-selected range. The value of i starts from 1 to the row number of Rng.
• Another for loop will run, where the value of j ranges from 1 to column number of Rng. The current cell’s value is then evaluated to the variable “Text“. The “Start” variable, which will be used to monitor the starting point of each new column, is then initialized to zero.

For k = 1 To c
            Rng2.Cells(i,((j - 1) * c) + k) = Mid(Text, Start + 1, Int(Arr(k - 1)))
            Start= Start + Int(Arr(k - 1))
        Next k

• Another for loop starts considering the value of k from 1 to c. The “Text” variable is used to extract a substring starting at the “Start” location and having a length determined by the value at the “k-1” index of the “Arr” array using the Mid function. Next, the value of Start is considered to be the sum of Start and the integer value of Arr.

Next j 

• This line ends the loop that iterates through each column in the range the user selects.

Next i 

• This line ends the loop that iterates through each row in the range the user selects.
• After that, simply click on F5 key on your keyboard to run the code.
• The following dialog box tilted Input will appear on your worksheet and you will be asked to select the range from where you want the texts to be separated.
• Select range C6:C10 and click on OK.

VBA Breakdown

Sub TextToColDelimiter()

• The “TextToColDelimiter” subroutine’s definition starts with this line.

Dim input_rng As Range
Dim output_rng As Range
Dim delimiter As String

• Input_rng and output_rng are Range type objects that will be used to hold the input and output ranges, respectively, and delimiter is a String variable that will contain the delimiter character. These lines define three variables.

On Error Resume Next
Set input_rng = Application.InputBox("Select the Range:", "ExcelDemy", Type:=8)
On Error GoTo 0

• These lines display an Excel dialog box and ask the user to choose a set of cells using the InputBox function. To handle any mistakes that might arise during the user’s selection process, use the “On Error Resume Next” and “On Error GoTo 0” statements.

If input_rng Is Nothing Then Exit Sub

• This line determines if input_rng has a value. If not, the procedure terminates right away.

On Error Resume Next
Set output_rng = Application.InputBox("Select the Output Range:", "ExcelDemy", Type:=8)
On Error GoTo 0

• These lines create a different dialog box and ask the user to choose an output range of cells in Excel using the InputBox function.

If output_rng Is Nothing Then Exit Sub

• This line determines whether output_rng has a value. If not, the procedure terminates right away.

delimiter = Application.InputBox("Enter the Delimiter Type:", "ExcelDemy")

• This line displays a dialog box and asks the user to type a delimiter character using the InputBox function. The delimiter variable will be updated with the character entered.

input_rng.TextToColumns _
    Destination:=output_rng, _
    DataType:=xlDelimited, _
    TextQualifier:=xlTextQualifierNone, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=True, _
    OtherChar:=delimiter, _
    fieldInfo:=Array(Array(1, 1), Array(2, 1)), _
    TrailingMinusNumbers:=True

• input_rng.TextToColumns, divide the text in the input range into distinct columns.
• Destination:=output_rng is the range to which the output should be written indicated.
• DataType:=xlDelimited indicates that a particular character serves as the text’s delimiter.
• TextQualifier:=xlTextQualifierNone instructs Excel to ignore any text qualifiers that may be present, such as quotes.
• ConsecutiveDelimiter:=False instructs Excel to treat multiple delimiters as one delimiter.
• Tab:=False, Semicolon:=False, Comma:=False, and Space:=False arguments specify that Excel should not treat these specific characters as delimiters (i.e. only the OtherChar delimiter should be used).
• Other:=True specifies that Excel should use the delimiter character specified in the OtherChar argument.
• OtherChar:=delimiter specifies the delimiter character.
• fieldInfo:=Array(Array(1, 1), Array(2, 1)) specifies how the columns should be split. In this case, it specifies that the first column in the input range should be split into two columns in the output range.
• TrailingMinusNumbers:=True specifies that Excel should treat any minus sign at the end of a cell as a negative number.
• Run the code by pressing F5 and you will be asked for the input range.
• Select range B5:B13, then click on OK.