[Solved] Convert column of unequal ID#'s to delimited text with equal space

[r.coon]

I want to convert a list 50-100 serial #'s of varying length to comma delimited text with equal character spaces used.

COLUMN EXAMPLE:
12345678
1n234567
1n234567-1
RCn234567
12345678-90
6x789012-34

Converted to: 12345678 , 1n234567 , 1n234567-1 , RCn234567 , 12345678-90, 6x789012-34

Pasting this line of text into form fields of limited width force it into 2-4 columns of equal width making it easier to read.

Sample pasted into small field that forces auto-wrap:
The snip of Notepad file emulates the form field because it uses simple font where all characters use same amount of space.

I've tried to CONCAT, TEXTJOIN and Find/Replace in MS Word (doesn't recognize column markers as delimiters marks), just can't figure out how to maintain constant (in this case 11) character spaces.

 

[Aniruddah Alam]

I want to convert a list 50-100 serial #'s of varying length to comma delimited text with equal character spaces used.

COLUMN EXAMPLE:
12345678
1n234567
1n234567-1
RCn234567
12345678-90
6x789012-34

Converted to: 12345678 , 1n234567 , 1n234567-1 , RCn234567 , 12345678-90, 6x789012-34

Pasting this line of text into form fields of limited width force it into 2-4 columns of equal width making it easier to read.

Sample pasted into small field that forces auto-wrap: View attachment 1159
The snip of Notepad file emulates the form field because it uses simple font where all characters use same amount of space.

I've tried to CONCAT, TEXTJOIN and Find/Replace in MS Word (doesn't recognize column markers as delimiters marks), just can't figure out how to maintain constant (in this case 11) character spaces.

Dear R. Coon,

Thank you for reaching out with your query. I'm pleased to assist you! VBA is an excellent choice for achieving your goal, offering a straightforward solution compared to regular Excel formulas.

I've crafted a VBA code to seamlessly combine text from a selected range of cells.

Sub CombineAndSpaceText()
    ' Prompt the user to select a range of cells
    Dim selectedRange As Range
    On Error Resume Next
    Set selectedRange = Application.InputBox("Select a range of cells", Type:=8)
    On Error GoTo 0
    
    ' Check if the user canceled the operation
    If selectedRange Is Nothing Then
        MsgBox "Operation canceled."
        Exit Sub
    End If

    ' Find the maximum length of cell values in the selected range
    Dim maxLength As Long
    maxLength = 0

    For Each cell In selectedRange
        If Len(cell.Value) > maxLength Then maxLength = Len(cell.Value)
    Next cell

    ' Initialize variables for combining text and spacing
    Dim combinedText As String
    Dim spacingCount As Long

    ' Combine text with proper spacing
    For Each cell In selectedRange
        combinedText = combinedText & cell.Value
        spacingCount = maxLength - Len(cell.Value)
        combinedText = combinedText & String(spacingCount, " ") & ", "
    Next cell

    ' Remove the trailing comma and space
    combinedText = Left(combinedText, Len(combinedText) - 2)

    ' Print the result to the Immediate Window (for debugging)
    Debug.Print combinedText

    ' Display the result in cell E1, change it accordingly
    Range("E1").Value = combinedText

 ' Copy the result to the clipboard using DataObject
    Dim dataObj As Object
    Set dataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    dataObj.SetText combinedText
    dataObj.PutInClipboard

    ' Notify the user that the result has been copied to the clipboard
    MsgBox "Result copied to clipboard."
End Sub

The code ensures uniform text size and adds commas for clarity. It prompts you to select the desired cell range, then places the resulting combined text in cell E1. Additionally, it conveniently copies the text to your clipboard for easy pasting into applications like Notepad.

Feel free to use the provided code, and don't hesitate to reach out if you have any further questions or need additional assistance.