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
thao-nhiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
A small sample file along with the desired output would be helpful.
0
thao-nhiAuthor Commented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
Robberbaron (robr)Commented:
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
0
aflockhartCommented:
based on your short sample I would:


1 -if the data is in a text file, open this file to import it.
2 -  use the options available during the import, or select the coulmn containing e raw data and usethe Text To Columns command, to divide the data into columns using the semicolon ";" as the delimiter
3 -  Delete the columns that you don't want, leaving three columns A B and C

These columns still contain multiple spaces in their text.  If these were standard space characters you  remove them using the TRIM function  so if your data starts in row 1, you could enter a formula in D1 : =TRIM(a1)&";"&Trim(b1)&";"&TRIM(c1)

However, in the sample data you have posted, this doesn't work because the space characters are not ordinary spaces ( ASCII character 32) .  With a bit of testing, it seems they are ACSII character 160; so we can use a more complex formula to convert these to standard spaces and then remove them.  So in D1 we would enter:

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))&";"&TRIM(SUBSTITUTE(B1,CHAR(160),CHAR(32)))&";"&TRIM(SUBSTITUTE(C1,CHAR(160),CHAR(32)))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.