Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-03-2012, 05:33 AM
mjosic mjosic is offline Spliting column with text and numbers Windows XP Spliting column with text and numbers Office 2007
Novice
Spliting column with text and numbers
 
Join Date: Apr 2012
Posts: 2
mjosic is on a distinguished road
Default Spliting column with text and numbers

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
Reply With Quote
  #2  
Old 04-03-2012, 07:27 PM
Boatwrenchv8's Avatar
Boatwrenchv8 Boatwrenchv8 is offline Spliting column with text and numbers Windows 7 64bit Spliting column with text and numbers Office 2010 32bit
Novice
 
Join Date: Apr 2012
Posts: 23
Boatwrenchv8 is on a distinguished road
Default

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
Attached Files
File Type: xlsm One way to split columns for Mjosic.xlsm (21.1 KB, 20 views)
Reply With Quote
  #3  
Old 04-04-2012, 02:39 AM
mjosic mjosic is offline Spliting column with text and numbers Windows XP Spliting column with text and numbers Office 2007
Novice
Spliting column with text and numbers
 
Join Date: Apr 2012
Posts: 2
mjosic is on a distinguished road
Default

Hi Boatwrenchv8,

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

I owe you for this!

Best,

Miroslav
Reply With Quote
  #4  
Old 04-04-2012, 05:56 AM
Boatwrenchv8's Avatar
Boatwrenchv8 Boatwrenchv8 is offline Spliting column with text and numbers Windows 7 64bit Spliting column with text and numbers Office 2010 32bit
Novice
 
Join Date: Apr 2012
Posts: 23
Boatwrenchv8 is on a distinguished road
Default

Miroslav,

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

Regards,
Rich
Reply With Quote
Reply



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
Spliting column with text and numbers 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:26 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft