Is there a way to count delimiters before a text-to-columns is performed on a paste event?

[philwojo]

Hello, I have a sheet that data is being pasted in to and it should contain 57 fields, so 56 delimiters, in this case a comma. Is there a way to have VBA code to count the number of delimiters that are in the data on the paste event and display that number?

I need to check this because it is possible that the data could contain more or less that what should be there and since some data fields don't contain data you can't always tell if there is an extra or missing comma. Specifically the last 3 fields should all be blank, but the data needs the comma's there for the file to be read properly by another tool.

Thanks,
Phil

 

[philwojo]

OK, I think I got it to work, probably not the best way, but it does run and appears to do what I need it to do.

Here is my updated code in the 2nd portion:

Sub Text2ColSplit()    Range("b7:b6000").TextToColumns _
      Destination:=Range("b7:b6000"), _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Comma:=True


End Sub

If someone has a cleaner way to do it I'm always up for learning.

 

[Marcelo Branco]

My improved version

Sub aTest()
    'Count commas on each line
    Dim myFile As String, textline As String
    Dim dic As Object, i As Long, vKey As Variant
    Set dic = CreateObject("Scripting.Dictionary")
    
    myFile = Application.GetOpenFilename()
    Open myFile For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
    Do Until EOF(1)
        i = i + 1
        Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , textline
        dic(i) = Len(textline) - Len(Replace(textline, ",", ""))
    Loop
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
    'Put the results to a sheet <--- adjust sheet name and ranges
    'Headers
    Sheets("Sheet1").Range("A1:B1") = Array("Line", "Num of commas")
    'Results
    With dic
        Sheets("Sheet1").Range("A2").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
    End With
End Sub

Hope this helps

M.

 

[Truiz]

Sorry to come in late but if I understood correctly this should do it.

Sub ASNEE()

Dim lRow, i As Long
'--------------------------Count Commas------------------------------------------------------------------------------
lRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row


For i = lRow To 1 Step -1
    Cells(i, 1).Value = Len(Cells(i, 2).Value) - Len(Replace(Cells(i, 2).Value, ",", "")) 'You need to select a different column or sheet
Next                                                                                      'to paste the information otherwise it'll get overwritten


'---------------------------Text-to-Columns--------------------------------------------------------------------------


Range("B7:B" & lRow).TextToColumns Destination:=Range("B7"), DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True


End Sub

 

[Truiz]

Sub ASNEE()

Dim lRow, i As Long
'--------------------------Count Commas------------------------------------------------------------------------------
lRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row


For i = lRow To 1 Step -1
    Cells(i, 1).Value = Len(Cells(i, 2).Value) - Len(Replace(Cells(i, 2).Value, ",", "")) 'You need to select a different column or sheet
Next                                                                                      'to paste the information otherwise it'll get overwritten


'---------------------------Text-to-Columns--------------------------------------------------------------------------


Range("B7:B" & lRow).TextToColumns Destination:=Range("B7"), DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True




'---------------Bonus just for fun and giggles removing blank cells---------------------------------------------------


For i = lRow To 7 Step -1
    Rows(i).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
Next
End Sub

 

[Truiz]

Did it work the way you wanted?

 

[philwojo]

Sorry I have not yet had a chance to try it out, been busy with putting out other fires at work. I'll try and get to this today and post back.

I appreciate the assistance though.

Phil

 

[Marcelo Branco]

Sorry I have not yet had a chance to try it out, been busy with putting out other fires at work. I'll try and get to this today and post back.

I appreciate the assistance though.

Phil

Phil,

If i understood your question correctly, you do want to count the delimiters (comma) ,on each line of .txt file, before Text-to-Columns is performed.
If so, try my code in post 12.

M.