Extract Words with Delimiters from a String where more than one occurs in the string

[noboffinme]

Hi Forum,

I have many rows of text where there are values delimited by '[' , ']', '(' , ')' , & also by ' and '.

Example;

Please Login or Register  to view this content.

I've got this example data in Excel in Column 'A' and wanted to copy the values (with Delimiters) to Column B, then C, then D etc, until there are no more occurrences of the Delimiters for that row.

So the desired output for this example going from Column 'B' would be as below;

Col B-----Col C-----Col D-----Col E------Col F--------Col G-----------Col H
[Calc]---'ABC123'--[Acct]--- 'Rev'--- [Desc]---'05 - Central'---([Amt]/0.1)

The values could also go beyond Column 'H'

I've had a look at some examples but they have been extracting all text between the first & the last '[' where there's more than one value in '['.

I've also investigated going step by step through each string and apply an If Statement to show the position number inside the string where it equals '[' but there's got to be a better way.

Thanks Pete

[Glenn Kennedy]

This is probably achievable with a formula, too. However, we need more than ONE sample. We need about 10-20 rows of FULLY representative data.

The separators are ELSEIF AND THEN =, <> and perhaps others.... but what others????

Confirm that you are ONLY interested in a VBA solution, that you are using a PC (at least Excel 2013... and not a Mac) and provide a sample sheet.

[mohadin]

Hi
For this particular example you summitted
And for PC Not Mac
you might try this code

Please Login or Register  to view this content.

[noboffinme]

Thanks Glenn,

I appreciate your response, but I think mohadin has the answer I need.

Pete

[noboffinme]

Hi mohadin,

I think this code is missing the first string with '[' and ']' delimiters.

Can your code add this in?

Thanks

[mohadin]

Thank you
Do you mean This

Please Login or Register  to view this content.

[noboffinme]

Hi mohadin,

This still misses the first string inside the first '[ ] ' brackets?
Are these regular expressions in a certain order as they could be in any order?

Thanks

[mohadin]

Are these regular expressions in a certain order as they could be in any order?

Not necessarily as you can see in this

Please Login or Register  to view this content.

[noboffinme]

Thanks but this still misses the first string inside the first '[ ] ' brackets?

[jindon]

Please Login or Register  to view this content.

[mohadin]

Try

Please Login or Register  to view this content.

[noboffinme]

Thanks jindon,
Perfect!

[noboffinme]

Thanks again mohadin

[jindon]

You are welcome and thanks for the rep.