CSV file with Comma's between quotation marks - Reg Expression - CSV to Array

  • Hi All,

    Interesting problem i recently had with CSV files to Array syntax i usually use...

    The above method will be ok If you dont have numbers with leading zero's as they will be lost

    Example line from CSV file with ten columns but is complicated with commas between quotation.

    "012,034 ABCDE,,,"EFG,56,,,HIJ,,KLMN",,56"OP,QR,ST,",07890,UVWXYZ,"

    Rewritten for the VBA Module

    "012,034 ABCDE,,,""EFG,56,,,HIJ,,KLMN"",,56""OP,QR,ST,"",07890,UVWXYZ,"

    Reg Ex Example to replace comma between quotations with Pipes

    VBA example

    Putting it all together

    Example two

    The Pipe's can be removed from the work sheet with something like

    With .UsedRange
    .Replace What:="|", Replacement:=",", SearchOrder:=xlByColumns, MatchCase:=True

    Hope this helps...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!