Extract text from second set of brackets

Hi Excel masters,

I have a string of text in one cell that's broken up by multiple brackets. The brackets include a date stamp and the name of the person who changed the record. It looks like this:

[MMM-DD-YY fname lname] desired text 1

[MMM-DD-YY fname lname] desired text 2

[MMM-DD-YY fname lname] desired text 3[

(The end bracket "[" after "desired text 3" is intentional--it's been concatenated onto the text string.)

What I'm trying to do is find a formula to extract "desired text 2" into one cell and then "desired text 3" into a separate cell, but I don't understand how to find the second and third set of brackets. Does anyone know of a formula that would work?

Thanks!

It's hard to point you on a good track without seeing actual example and their results (a small spreadsheet would be great). I would look into DATA, Text To Columns - in my example, I used DELIMITED, and for the delimiters, I had TAB, SPACE and OTHER selected. In the OTHER, i placed the right bracket "]" (don't enter the quotes)....this separated/ parsed my data into the separate fields. Do some experimenting and you'll probably get it. PS this will put the items you are separating into the columns next to them, so be sure to have room OR copy them to another location.

Thank you both! @queuesef, I can't add any additional columns, unfortunately, because it would mess with the integrity of the spreadsheet.

@Glenn: your did work, but the returned text was cut off at 11 characters. The text I want pulled isn't uniform in terms of the number of characters, so is there a formula that will return everything up until the next "["?

I misspoke--it doesn't cut off the characters at 11, but for some reason part of the output text has been cut off for several of the cells. The others are working perfectly and returning the entire text up until the next open bracket. It doesn't seem like the length of the text string matters, since some longer ones are returning just fine and some shorter ones are being cut off. Do you know what's going on?

I can upload a spreadsheet of what I'm seeing, but I don't know if I could replicate what's happening after anonymizing the data.

try pasting them into another tab or worksheet....see if it gets you desired results.

The vast majority (~90%) only have one character. The longest number of characters is 1607. This field is is all of the notes on a sales record combined together, so there's a lot of variety.

I was able to create a formula that pulls the text starting at "desired text 2", but I'm not able to cut off the rest of the string. Here it is:

Formula:

Please Login or Register  to view this content.