Spliting column with text and numbers

mjosic · #1 04-03-2012, 05:33 AM

Hi people,

I have one problem and would very appreciate your help.

I've got a txt file with lots of data. In first column I have Country and for each country code of export product (but first on 4 digits as a category and than full codes for each product which is 8-10 digits), in second product details and in the third one export quantity. When I transfer the file into Excel, the table is following.

Country/Code Description Values in EUR

Afghanistan
3002 aaaaaaaaaaa 25.568.789
300220000 aaaaaaaaaaa 25.568.789
3005 aaaaaaaaaaa 20.145.698
300590211 aaaaaaaaaaa 5.000.000
300590311 aaaaaaaaaaa 15.145.698
8201 aaaaaaaaaaa 7.236.547
820110001 aaaaaaaaaaa 5.236.547
820110002 aaaaaaaaaaa 2.000.000
....
....
China
030371102 aaaaaaaaaaa 25.568.789
040110105 aaaaaaaaaaa 15.145.698
05040000 aaaaaaaaaaa 7.236.547
...
...
..

and smth about 100 000 rows.

The question is how to divide this first column in two columns, so the table in the end looks like this:

Country Code Description Values in EUR

Afghanistan 300220000 aaaaaaaaaaa 25.568.789
Afghanistan 300590311 aaaaaaaaaaa 15.145.698
Afghanistan 300590211 aaaaaaaaaaa 5.000.000
Afghanistan 820110002 aaaaaaaaaaa 2.000.000
Afghanistan 820110001 aaaaaaaaaaa 5.236.547
China 030371102 aaaaaaaaaaa 25.568.789
China 040110105 aaaaaaaaaaa 15.145.698
China 050400007 aaaaaaaaaaa 7.236.547

Assuming VB should do it or???

Thanks a lot!!!

Miroslav

Boatwrenchv8 · #2 04-03-2012, 07:27 PM

Hello. This is one way of splitting the columns with text and numbers. It isn't the best or the only way but given your sample data, I have accomplished it. A macro enabled workbook is attached at the bottom of this post. The steps are included on the sheets, please start at Sheet Number 1. The code that is in the workbook is also included below if you need it.

Code:

Sub CleanUpVer1()
'
' CleanUpVer1 Macro
' IMPORTANT:  Start in Cell A1
'
'This macro will start in cell A1
'   and check each Row 's A and B cell's
'   value.  If Cell A and Cell B are the
'   same, the macro will delete that row
'   of data.  If Cell A and Cell B are
'   different, the macro will skip to the
'   next row and continue to check each
'   value until both Cell A and Cell B
'   are blank.
'
' Keyboard Shortcut: Ctrl+w
'
    Dim ItemInCellA As Variant
    Dim ItemInCellB As Variant
 
    Do
 
        ItemInCellA = Selection
        ActiveCell.Offset(0, 1).Range("A1").Select
        ItemInCellB = Selection
 
            If ItemInCellA = ItemInCellB Then
               Selection.EntireRow.Delete
               ActiveCell.Offset(0, -1).Range("A1").Select
            Else
               ActiveCell.Offset(1, -1).Range("A1").Select
            End If
 
    Loop While (ItemInCellA <> "") And (ItemInCellB <> "")
 
End Sub

Rich

mjosic · #3 04-04-2012, 02:39 AM

Hi Boatwrenchv8,

thanks a lot for this!!!! It worked perfectly for the entire set of data.

I owe you for this!

Best,

Miroslav

Boatwrenchv8 · #4 04-04-2012, 05:56 AM

Miroslav,

You're welcome. Happy I could help. Please mark this thread as being solved.

Regards,
Rich

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA column header vs first row text	vthomeschoolmom	Excel Programming	1	03-10-2012 02:05 AM
Different column numbers, but same place on page -- what is going on?! Help	Straitsfan	Word	3	03-01-2012 03:05 PM
Two column format without affecting neither the page numbers nor the header or footer	Jamal NUMAN	Word	4	02-21-2012 03:20 PM
Aligning column text in a table	dpeattie	Word	6	12-29-2010 04:59 PM
Can I auto increase numbers in a column?	compact	Word	1	09-10-2009 07:16 AM