Use VBA to build excel worksheet from text data

Question

We have some SPC software that will export data in the format below, We need to clean it and import it into excel, knowing that the measurements and the number of items being measured will change.

": Height__0.0:"
"NOM (LSL, USL) = 8.8428 (8.7828, 8.9028)"
"Subgroup"  5   
"Status"    "OS"    
"X" 8.8394  
"R" 0.1547  
"s" 0.0273  
"S1"    8.8445  
"S2"    8.8493  
"S3"    8.8450  
"S4"    8.8465  
"S5"    8.8483  
"S6"    8.8443  
"S7"    8.8315  
"S8"    8.8322  
"Inspector" "kh"    
"Machine"   "a12"   
"Sequence"  1-30    
"Station"   71445091    
"Material"  " " 
"Date/Time" " " 
"Time"  "10:23:02"  
"Date"  "03/18/2015"    

": Bead__45.0:"
"NOM (LSL, USL) = 0.8970 (0.8890, 0.9050)"
"Subgroup"  5   
"Status"    ""  
"X" 0.9013  
"R" 0.0050  
"s" 0.0012  
"S1"    0.9011  
"S2"    0.9005  
"S3"    0.8991  
"S4"    0.9014  
"S5"    0.9011  
"S6"    0.9017  
"S7"    0.9022  
"S8"    0.9019  
"Inspector" "kh"    
"Machine"   "a12"   
"Sequence"  1-30    
"Station"   71445091    
"Material"  " " 
"Date/Time" " " 
"Time"  "10:23:02"  
"Date"  "03/18/2015"    

I need to build this data into an excel worksheet as so

Height__0.0     Bead__45.0
NOM 8.8428      NOM 0.8970
LSL 8.7828      LSL 0.8890
USL 8.9028      USL 0.9050
8.8445          0.9011
8.8493          0.9005
8.845           0.8991
8.8465          0.9014

etc... Any advice where to start?

Answer 1

Well if it's always in this exac format this code can produce the table you want, you must paste the original code on the A column and run the macro, it will create the table on the B and C column:

Sub test()

Dim c1           As Integer
Dim c2           As Integer

c1 = 0
c2 = 0

Do While c1 < 25

Cells(1, 2 + c2) = Mid(Cells(1 + c1, 1), 4, WorksheetFunction.Search(":", Range("A1"), 3) - 4)
Cells(2, 2 + c2) = "NOM " & Mid(Cells(2 + c1, 1), 19, 6)
Cells(3, 2 + c2) = "LSL " & Mid(Cells(2 + c1, 1), 27, 6)
Cells(4, 2 + c2) = "USL " & Mid(Cells(2 + c1, 1), 35, 6)
For i = 5 To 8
Cells(i, 2 + c2) = Mid(Cells(i + 3 + c1, 1), 9, 6)
Cells(i, 2 + c2).NumberFormat = "0.0000"
Next

c2 = c2 + 1
c1 = c1 + 24

Loop

End Sub

If you'd like to use the last numbers with "." as decimal separator just place this line

Cells(i, 2 + c2) = "'" & Mid(Cells(i + 3 + c1, 1), 9, 6)

in the place of this 2

Cells(i, 2 + c2) = Mid(Cells(i + 3 + c1, 1), 9, 6)
Cells(i, 2 + c2).NumberFormat = "0.0000"