Extract several dates from a string

[agerson]

I have a spreadsheet with quantities and dates embedded in the text string. I would like to extract the Qty, Date, Qty, Date ... into up to 8 separate sets of columns so that I can work with the different dates and quantities separately.

The data is currently in the following format, with the [701.4] being the Qty, followed by the date. Each separate Order #, Date & Qty are grouped in parenthesis ()

(L-8735:3[701.4][22/09/2015])(L-8844:5[458.32][30/10/2015])
(L-8744:5[935.2][22/09/2015])(L-8792:3[1402.8][29/10/2015])(L-8802:3[2104.2][29/10/2015])

Can anyone recommend a macro to separate these or a formulae that may work

Thanks
Allen

[FDibbins]

Hi, welcome to the forum

Not VBA, but perhaps a slightly manual approach that may work for you.

1. with your data in A2, copy this down in B2...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(","@"),"[","@"),"]","@"),")","@")

2. Copy/paste values either into a new column, or over the formulas
3. Use Text2Columns/delimited/Other/@

[jindon]

VBA

Please Login or Register  to view this content.

[AlKey]

Maybe this one

Enter formula in B2 and pull it to the right until you see blanks.

=TRIM(MID(SUBSTITUTE("|"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"[","|"),"]","|"),"(",""),")",""),"||","|"),"|",REPT(" ",255)),255*COLUMNS($A:A),255))

SplitCell.JPG

[danwagnerco]

This is a great question, and if you would like to learn more about how the code below works you can learn about everything that's happening, line-by-line (and withe additional notes on how I landed on the RegExp matcher, for example), check out this post:

How Can You Extract Several Values from a String?

The macro below will pluck an arbitrary (meaning 2, or 3, or 4, or even up to 8 as you mentioned in your question) number of square bracket-enclosed values from the complicated strings you are facing.

In order to run the script below, you'll need to ensure the "Microsoft VBScript Regular Expressions 5.5" library is enabled:

turn_on_regular_expressions.png

From there, this heavily-commented code should do the trick!

Please Login or Register  to view this content.