Convert unformatted text in excel to columns

[DaLiMan]

Hi,

I have a lot of unformatted text (wich differs slightly in lenght) which I need converted to excel columns.

I've been trying in the sheet with LEFT,MID and RIGHT and stuff but cannot figure it out.

Is there anyone who has done something like this or has a good idea how to proceed?

Below is a part of the text:
Every bold/non-bold text is a new column and basically is every SPACE a new column.
This is true for all number, however the caracters are one column and have spaces in them...
This is the big issue I think....

Maybe with some fancy VBA?

1 951954 LAUF WANDCLOSET SUPREMA WIT 197,00 50 98,50 98,50 11,64
1 3545951 PRES PROJECTA CLOSZ.ZD WIT BY3 56,70 45 31,19 31,19 9,43
1 2339711 GEB DUOFIX WC-ELEM FRONTB 290,70 54,75 131,53 131,54 0,30
0 2447589 GEB DUOFIX WC-ELEM FRONTB PL12 2,185,87 28,18 1,569,99 0,00 3,71 39
1 2621761 GEB MAMBO BEDPAN RVS 102,12 17 - 30 59,33 59,33 22,48
1 727073 GESA ECONOMY CLOSROLH 2146 4,98 17 - 32 2,81 2,81 17,08

 

[andrewkard]

Hello, maybe DATA - text to columns can help?

 

[DaLiMan]

Hello, maybe DATA - text to columns can help?

Normally text-to-colums (T2C) is a quick and easy way to do such a thing.
T2C can filter the spaces but then also the caracters (wich are text and need to stay together) are spread across columns....
I need the numbers in different columns but all text in 1 column.

 

[andrewkard]

try:

Sub test()
    Dim i&, lLr&, l&
    Dim aArr$()
    Dim sStr$
    
    lLr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To lLr
        lCn = 3
        sStr = Replace(Cells(i, 1).Value, ",", ".")
        aArr = Split(sStr, " ")
        For l = 1 To UBound(aArr)
            If Val(aArr(l)) > 0 Then
                Cells(i, lCn) = aArr(l)
                lCn = lCn + 1
            Else
                Cells(i, 2) = Cells(i, 2).Value & " " & aArr(l)
            End If
        Next l
    Next i
End Sub

 

[DaLiMan]

try:

Sub test()
    Dim i&, lLr&, l&
    Dim aArr$()
    Dim sStr$
    
    lLr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To lLr
        lCn = 3
        sStr = Replace(Cells(i, 1).Value, ",", ".")
        aArr = Split(sStr, " ")
        For l = 1 To UBound(aArr)
            If Val(aArr(l)) > 0 Then
                Cells(i, lCn) = aArr(l)
                lCn = lCn + 1
            Else
                Cells(i, 2) = Cells(i, 2).Value & " " & aArr(l)
            End If
        Next l
    Next i
End Sub

very nice
maybe when I tweak it a bit it will be more than usefull.

Thanx!

Daniel