asked on

Microsoft Excel 2010,

I have a delimited data file as below. What I want to do is to clean it up as follow
SAX1801-B--C-6;BLACK LEATHER STRAP 9110 SAXO WITH HOLES WITH BUCKLE; 38

Please let me know how to loop through each row of the entire file and get the result as described above using Excel 2010

thanks

SAX1801-B--C-6 ; ;BLACK LEATHER STRAP 9110 SAXO WITH HOLES       WITH BUCKLE;1;38;38
SAX1401-B--C-2 ; ;BLACK LEATHER STRAP 9910 SAXO WITH HOLES       WITH BUCKLE BLACK STITCHES;1;38;38
SA2202-BDB-XS-R8 ; ;STRAP 22mm SMOOTH BROWN XS FOR 4873 WITH HOLES       NO BUCKLE;1;38;38
SA2202-BDB-XL-R8 ; ;STRAP 22mm SMOOTH BROWN XL FOR 4873 MODEL WITH HOLES       WITH BUCKLE BLACK STITCHES;1;38;38
SA2202-BDB--R8 2 ; ;STRAP 22mm SMOOTH BROWN FOR 4873 MODEL WITHOUT HOLES       FOR CLASP CL73;1;49;49
SA2202-BDB--R8 ; ;STRAP 22mm SMOOTH BROWN FOR 4873 MODEL WITH HOLES       NO BUCKLE;1;49;49
SA2202-BAB-XL-R8 ; ;BROWN LAMB STRAP 9973 XL WITH STEEL BUCKLE       WHITE STITCHES;1;38;38
SA2202-BAB--R8 ; ;STRAP SMOOTH 22mm BROWN FOR 9973 WITH STEEL BUCKLE       WHITE STITCHES;1;49;49
SA2201-BDB-XL-R8 ; ;STRAP SMOOTH 22mm BLACK XL NO BUCKLE 4873 WHITE STITCHES w/HOLES       FOR CLASP CL72;1;73;73

Subodh Tiwari (Neeraj)

A small sample file along with the desired output would be helpful.

thao-nhi

ASKER

Sample file:

SAX1801-B--C-6 ; ;BLACK LEATHER STRAP 9110 SAXO WITH HOLES WITH BUCKLE;1;38;38
SAX1401-B--C-2 ; ;BLACK LEATHER STRAP 9910 SAXO WITH HOLES WITH BUCKLE BLACK STITCHES;1;38;38
SA2202-BDB-XS-R8 ; ;STRAP 22mm SMOOTH BROWN XS FOR 4873 WITH HOLES NO BUCKLE;1;38;38
SA2202-BDB-XL-R8 ; ;STRAP 22mm SMOOTH BROWN XL FOR 4873 MODEL WITH HOLES WITH BUCKLE BLACK STITCHES;1;38;38
SA2202-BDB--R8 2 ; ;STRAP 22mm SMOOTH BROWN FOR 4873 MODEL WITHOUT HOLES FOR CLASP CL73;1;49;49
SA2202-BDB--R8 ; ;STRAP 22mm SMOOTH BROWN FOR 4873 MODEL WITH HOLES NO BUCKLE;1;49;49
SA2202-BAB-XL-R8 ; ;BROWN LAMB STRAP 9973 XL WITH STEEL BUCKLE WHITE STITCHES;1;38;38
SA2202-BAB--R8 ; ;STRAP SMOOTH 22mm BROWN FOR 9973 WITH STEEL BUCKLE WHITE STITCHES;1;49;49
SA2201-BDB-XL-R8 ; ;STRAP SMOOTH 22mm BLACK XL NO BUCKLE 4873 WHITE STITCHES w/HOLES FOR CLASP CL72;1;73;73

Desired output
SAX1801-B--C-6;BLACK LEATHER STRAP 9110 SAXO WITH HOLES WITH BUCKLE; 38

thanks

Subodh Tiwari (Neeraj)

Please don't paste the sample data here itself rather upload a sample file with some data then only someone would be able to help you. As that would help us to know the possible variation in the raw data and also remember to show the desired output for few records.

Robberbaron (robr)

try this vba...

Option Explicit
Sub FixBucklesFile()
    Dim filenameIn As String, filenameOut As String
    Dim ffn1 As Long, ffn2 As Long, ix As Integer
    Dim rawtxt As String, nicetxt As String
    Dim itemcount As String
    
    filenameIn = Range("Input_file").Value
    
    filenameOut = Range("Output_file").Value
    
    ffn1 = FreeFile
    Open filenameIn For Input As ffn1
    ffn2 = FreeFile
    Open filenameOut For Output As ffn2
    
    Do While Not EOF(ffn1)
        Line Input #ffn1, rawtxt
        'get rid of double spaces
        nicetxt = rawtxt
        For ix = 1 To 20
            nicetxt = Replace(nicetxt, "  ", " ")
        Next ix
        'get rid of single space near semicoln
        nicetxt = Replace(nicetxt, "; ", ";")
        nicetxt = Replace(nicetxt, " ;", ";")
        
        'now delete double semicolon
         nicetxt = Replace(nicetxt, ";;", ";")
         
        'drop last set on numbers
        ix = Len(nicetxt)
        ix = InStrRev(nicetxt, ";", ix - 1)  'fiest ;
        itemcount = Mid$(nicetxt, ix + 1, 99)
        ix = InStrRev(nicetxt, ";", ix - 2) 'second ;
        ix = InStrRev(nicetxt, ";", ix - 2)  'third ;
        nicetxt = Left$(nicetxt, ix) & itemcount
        Print #ffn2, nicetxt
    Loop
End Sub

Open in new window

buckles.xls
buckles-raw.txt
buckles-nice1.txt

ASKER CERTIFIED SOLUTION

aflockhart

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial