Splitting values within a cell when it contains parentheses without using Text to Columns

[Eades1412]

Hi all,

I have, what I think may be a simple request where I have a row of data with cells that have values separated by delimiters (parentheses and commas). I would like to split each value into a separate cell. However, I am unable to do this using Text to Columns, because when I split using the comma delimiter, it splits values that I do not want. Please see attached workbook.

Column A is the original values. The descriptive words (i.e. 'Fields') are separated by numbers in brackets (i.e. 'Percentage'). I would like to split each of these values into separate columns. So Columns B-G will have the separate values. Is there a formula for this?

Thank you!
S.

[AliGW]

In B2 copied across and down:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"(","|"),")","|"),"|",REPT(" ",LEN($A2))),", ",""),(COLUMNS($A:A)-1)*LEN($A2)+1,LEN($A2)))

[WideBoyDixon]

Tricky. Using formulas I think you'll need some helper columns. The alternative is to use a UDF. Both solutions are in the attached.

WBD

[AliGW]

Can be done without helper columns and without VBA (see post #2).

[WideBoyDixon]

Or use the @AliGW solution! Must go and get coffee ...

WBD

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Eades1412]

@AliGW

This works perfectly! I was hoping there would be a more straight-forward and shorter formula, but it looks like it wasn't as 'simple' as I had initially thought. Thank you so much, AliGW!

[Eades1412]

Tricky. Using formulas I think you'll need some helper columns. The alternative is to use a UDF. Both solutions are in the attached.

WBD

This also worked exactly how I wanted it! I understand the 'ExtractValue' formula that you have used, but am a little lost with the 'Trim/Mid' formula because I am not familiar with 'helper' columns, but this did the trick regardless! Thanks so much for your help, WideBoyDixon!

[AliGW]

My formula isn't particularly long.

Thanks for the rep.

[romperstomper]

FWIW, you could also use:

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(LEFT(A2,LEN(A2)-1),"(","</y><y>"),"),","</y><y>")&"</y></x>","//y"))

which will spill across as necessary, then just fill down.

[Eades1412]

Wow, this is fantastic! I must admit, I am not familiar with this formula at all, but I copy and pasted it, and it worked. Thanks for your suggestion, rorya!

[WideBoyDixon]

How am I seeing FILTERXML for the first time? This is awesome! I'm definitely using this from now on to extract data from text.

WBD

[AliGW]

It's new in 365 - it's something I intend looking into now I'm retired. A fair few experienced members have already got their heads around it!!!

[romperstomper]

There's a good page on it here: https://exceljet.net/excel-functions...erxml-function with some useful examples at the bottom.

[Eades1412]

Thanks for the link to the examples for the FILTERXML function, rorya! This has saved my life! :D

[AliGW]

@rorya - thank you!

@Eades112 - saved your life? That's a bit OTT, I'd say!!!

I'm off to read that article ...

[TMS]

Late to the party, but .... Slight variation on a theme, Cell B2, copied down:

Please Login or Register  to view this content.

Adjust the F$1 if there will be more than six columns to be extracted.

Based on Rory's article and other examples, and some fine tuning from Rory's formula.