Extract string of numbers between braces

jdecayet · Mar 7, 2015

Please help extract the below string of numbers between braces. The string is located in cell A1.
I want to use SQL or VBA codes to extract 0.0593, 0.3240, 0.6967... 0.5347 from cell A1 and place them in their separate cells in another sheet.

Currently, Sheet 1 cell A1 contains:
{0.0593} {0.3240} {0.6967} {0.8059} {0.5232} {0.016} {0.2571} {0.028} {0.2672} {0.2357} {0.9829} {0.9147} {0.2501} {0.9076} {0.5915} {0.7498} {0.9464} {0.1989} {0.341} {0.7891} {0.2747} {0.3774} {0.8449} {0.0873} {0.9707} {0.0891} {0.8574} {0.0894} {0.1223} {0.5094} {0.9693} {0.0707} {0.9802} {0.5838} {0.0343} {0.3994} {0.5984} {0.4721} {0.039} {0.6538} {0.5928} {0.1375} {0.5706} {0.9789} {0.7281} {0.6833} {0.9465} {0.2731} {0.3951} {0.8262} {0.4888} {0.415} {0.9401} {0.4709} {0.1038} {0.5347}

Seeking the following results (Sheet 2):
A1: 0.0593 B1: 0.3240 C1: 0.6967 etc.
A2: 0.5915 B2: 0.7498 C2: 0.9464 etc.
A3: 0.1223 B3: 0.5094 C3: 0.9693 etc.
etc.

Any alternative method/s to extract them from one cell will greatly be appreciated.

Thank you.
jdecayet

NoS · Mar 7, 2015

Depending on what A1 exactly holds, perhaps something like this ?

Code:

Sub Test()
    Dim i As Integer
    Dim arr As Variant
    Dim r As Long
    Dim c As Long
    
    arr = Split(Replace(Replace(Replace(Sheet1.[A1], " ", "|"), "{", ""), "}", ""), "|")
    r = 1
    c = 1
    With Sheet2
        Do While i < UBound(arr) + 1
            .Cells(r, c) = arr(i)
                i = i + 1
                c = c + 1
                If c = 15 Then
                    c = 1
                    r = r + 1
                End If
        Loop
    End With
End Sub

snb · Mar 7, 2015

Code:

sub M_snb()
   sn=split(replace(range("A1"),"}","{"),vblf)

    for j = 0 to ubound(sn)
       sheet("sheet2").cells(j+1,1).resize(,14)=filter(split(sn(j),"{")," ",false)
    next
End Sub

Extract string of numbers between braces

jdecayet

New member

NoS

Member

snb

New member