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

 

[Marcelo Branco]

This?

Dim s As String, lCounter As Long

s = "blu, blah, blu bla, bla bla bla, bli"
lCounter = Len(s) - Len(Replace(s, ",", ""))
MsgBox lCounter

M.

 

[philwojo]

Kind of, the data will be the 57 fields, but it could be multiple rows of, each a unique entry, maybe up to 100 rows. It would be unique each time, there is not set number for how many rows could be pasted in at one time.

 

[Marcelo Branco]

It's hard to understand what you are trying to do without examples

M.

 

[philwojo]

Here is a sample of the data I have, as you can see some fields are blank, some are populated in the chart, but it starts off like this raw data above the table below. I want to be able to count the number of comma's that show up in the raw data before it is broken out via text-to-columns via a paste by a user. I want to make sure that there are 56 commas in that raw data.

Once the text-to-columns is done, which could be as soon as you paste it in if the sheet had it done previously, there is no way to know if all 56 commas, 57 fields, are present in the raw data. I can have anywhere from 1 line of data in the table to several hundred.

Does this help, and if not I apologize, I'm trying to give as much information as I can.

1,,,,,Activate,AP001,,Single-Use Account,,Add,,,2017-10-06 23:59:59,2017-11-05 23:59:59,,,0.00,0.00,USD,,,,,326,,INV# RS/EXP/01/2017-18 / 2017-07-15|INV# RS/EXP/02/2017-18 / 2017-07-15,Add|Add,27256.25|5678.75,1|1,Each|Each,,,,,,,,,,Add,950,,"Redisolve",Add,,,,,,,Vendor ID|Payment Number,Add,5751|326,,,
2,,,,,Activate,AP001,,Single-Use Account,,Add,,,2017-10-06 23:59:59,2017-11-05 23:59:59,,,0.00,0.00,USD,,,,,327,,INV# IDTI-10061 / 2017-07-21,Add,22152.00,1,Each,,,,,,,,,,Add,950,,"IDT-Systems Limited",Add,,,,,,,Vendor ID|Payment Number,Add,16454|327,,,
3,,,,,Activate,AP001,,Single-Use Account,,Add,,,2017-10-06 23:59:59,2017-11-05 23:59:59,,,0.00,0.00,USD,,,,,328,,INV# 38026439 / 2017-07-14|INV# 38026442 / 2017-07-14,Add|Add,108.00|292.00,1|1,Each|Each,,,,,,,,,,Add,950,,"Imaging Solutions AG WIRE PMT",Add,,,,,,,Vendor ID|Payment Number,Add,3352|328,,,

1

Activate

AP001

Single-Use Account

Add

2017-10-06 23:59:59

2017-11-05 23:59:59

0.00

0.00

USD

326

INV# RS/EXP/01/2017-18 / 2017-07-15|INV# RS/EXP/02/2017-18 / 2017-07-15

Add|Add

27256.25|5678.75

1|1

Each|Each

Add

950

Redisolve

Add

Vendor ID|Payment Number

Add

5751|326

2

Activate

AP001

Single-Use Account

Add

2017-10-06 23:59:59

2017-11-05 23:59:59

0.00

0.00

USD

327

INV# IDTI-10061 / 2017-07-21

Add

22152.00

1

Each

Add

950

IDT-Systems Limited

Add

Vendor ID|Payment Number

Add

16454|327

3

Activate

AP001

Single-Use Account

Add

2017-10-06 23:59:59

2017-11-05 23:59:59

0.00

0.00

USD

328

INV# 38026439 / 2017-07-14|INV# 38026442 / 2017-07-14

Add|Add

108.00|292.00

1|1

Each|Each

Add

950

Imaging Solutions AG WIRE PMT

Add

Vendor ID|Payment Number

Add

3352|328

<tbody>
</tbody>

 

[Marcelo Branco]

Still confuse - couldn't you simply count the number of columns after the Text-Column is done?

If not, i'm afraid i don't know how to help you. I do not have as much experience in dealing with text files (import), but i believe that it should be possible to loop and count the number of commas on each row.

Maybe someone else can help. Or try Google: Excel VBA import text files.

M.

 

[philwojo]

I can't count the columns because sometimes there is no data between comma's so it would be a blank field, and if there were extra blank fields after field #57 there is no way to see that once the text-to-columns has been executed.

I appreciate that assistance though, thank you for your time.

I am not importing the text file, just copying it from another source and then pasting it in to a single cell in excel.

I am considering maybe having the user paste the data in to a "input" type of sheet and then using macros to manipulate the data after that. I would then use code to make sure that the "input" or "raw data" sheet had code to always reset the text-to-columns back to default (of which I already have that code).

It just means more work to move the data to my other existing sheets now, but I might have to go that route.

Again, thanks for your assistance, and hopefully someone else might have some input.

Phil

 

[Marcelo Branco]

Maybe this can help...
https://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba

M.

 

[Marcelo Branco]

I made some searches in the internet

Maybe something like

Sub aTest()
    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] 
    
    For Each vKey In dic.keys
        MsgBox dic(vKey)
    Next vKey
End Sub

M.

 

[philwojo]

I appreciate you searching, I think I am looking at it slightly differently than you are.

I did the following so far.

Sub Test2()Dim copyRange As Range


Worksheets("PIF File Checker").Activate
Call Clearcells


Worksheets("Sheet1").Activate
   Set copyRange = Range("b2:b" & Range("A65536").End(xlUp).Row)
   With copyRange
       Sheets("PIF File Checker").Range("B7").Resize(.Rows.Count, .Columns.Count).Value = .Value
   End With
   
Worksheets("PIF File Checker").Activate
Call Text2ColSplit


End Sub

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


End Sub

This almost does what I want it to do, but it only does the text-2-columns for the 1st line of data that is put on the "PIF File Checker" sheet. How do I make it loop through and do all lines of data that were moved over. I'm not very strong in VBA yet, I can usually tweak samples I find online, but don't know enough to code it myself yet.

Phil