Manipulating strings

Hello,

I want to manipulate strings of text/numbers. See attached my spreadsheet.

If part of the string has one number in parentheses e.g. A(1), that can be left as it is. For parts with more than one number e.g. F(1,100,200), I want this broken down into F(1),F(100),F(200).

The "Original" is my string currently and "New" is what I want it to finish as.

I have hundreds of such strings so it cannot be done manually. Any suggestions/assistance will be appreciated.

Thanks.

Thanks. Using the code, how could I convert the string text into columns?

Hi
You can try this approach with this code in a module

Option Explicit

Function ExpandStr_Col(ByVal rng As Range, col As Integer) As Variant

On Error Resume Next

    Dim s As String, f As String, r As String
    Dim i As Integer, j As Integer, k As Integer, m As Integer, w As Integer
    
    s = rng.Value: k = 1: f = ""
    Do
        i = InStr(k, s, "("): r = Mid(s, k, i - k): j = InStr(i + 1, s, ")")
        f = f & r & "("
        For m = i + 1 To j
            If Mid(s, m, 1) = "," Then f = f & ")," & r & "(" Else f = f & Mid(s, m, 1)
        Next m
        If m < Len(s) Then f = f & ","
        k = m + 1
    Loop Until k >= Len(s)
    w = Len(s) - Len(Replace(s, ",", "")) + 1
    For i = w To col: f = f & ",": Next i
    
    ExpandStr_Col = Split(f, ",")
    
End Function

and using a array formula (need to be enter with Ctrl+Shift+Enter)
Select C2:Q2 and use this formula

Formula:

=ExpandStr_Col(A2,15)

where 15 is the maximum length of the array. (you can change that)
Select C2:Q2 and copy down

See the file

Thank you both for your help.

I have noticed that some of my strings do not include any parentheses at all e.g. A(1),BA(1),C1(1,100),D1A(10),E(1),F(1,100,200),G(1),H,J,K,L,M

Using the code in the first spreadsheet, such a string returned a #VALUE! error and in the second spreadsheet my Excel crashed. Could you assist in modifying the code to incorporate such instances?

Hi
Use in C2 this

Formula:

=ExpandStr(A2)

to expand the string to your desired format and then use in D2

Formula:

=Text2Column($C2,COLS($A$1:A$1))

and copy right to perform Text to columns.

See the file