Convert unformatted text in excel to columns

DaLiMan

Active Member
Joined
Jun 1, 2004
Messages
295
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

DaLiMan

Active Member
Joined
Jun 1, 2004
Messages
295
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

Active Member
Joined
Apr 6, 2012
Messages
455
try:
Code:
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

Active Member
Joined
Jun 1, 2004
Messages
295
try:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top