Separate text from text field to individual columns

[alegall]

First thank you for the assistance.
What I am trying to do if possible is pull individual peices of text from a single text field that is imported from a web page. This is what it looks like:

How to Setup External Telnet or SSH Client ( 2 ) [Wiki] (21)

This is what I am trying to accomplish:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]How to Setup External Telnet or SSH Client ( 2 ) [Wiki] (21)[/TD]
[TD]How to Setup External Telnet or SSH Client[/TD]
[TD]2[/TD]
[TD]Wiki[/TD]
[TD]21[/TD]
[/TR]
</tbody>[/TABLE]

Found plenty of posts using 'RIGHT', 'LEFT', 'FIND', etc. but have not been able to figure out how to break each piece out like this.
Any assistance would be fantastic.

 

[shawnhet]

Is the location of the brackets always the same and the values in the cells correspond to the values in each set of brackets?

 

[alegall]

The location of the '()' and '[]' are never in the same place because the initial string may be very long or short. So what I placed in column 'B' would be the title extracted from the full string in column 'A'. That is easy using 'LEFT' or 'RIGHT'. What I cant figure out is columns C, D, E. The value in those columns are the values surrounded by '()' and '[]' in column A.

Thank you.

 

[AlexandraT]

I suggest you try "Text to Columns" in the DATA tab. You can use a bracket as a delimiter. However, you can only choose one delimiter at a time so you either repeat with each new column or you use Find/Replace to replace all brackets to a single type of bracket.

I replaced ") [" with "(" and "] (" with "(" and ")" with "(" so I got rid of all other brackets and spaces except "(". After that a simple Text to Columns gave me what you need.

Or you can use SUBSTITUTE() instead of Find/Replace.

Alex

 

[alegall]

Thank you for the assistance. The thing is that I need to be able to do this for many entries in the worksheet. replacing each piece and then performing a text to column operation is not easily repeatable. I trying to come up with a couple of formulas that I can drag down the worksheet and have it work most of the time without intervention.
I know this is going to be difficult in excel, which is why I posted here. If anyone would knew how to do this, they would be here.

 

[mikerickson]

How about
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"[","("),")"," "),"]"," "),"(",REPT(" ",100)),1+100*(COLUMN(A:A)-1),100))


first we replace [ with (, then turn ) and ] into spaces and then split on the ( delimiter

 

[Rick Rothstein]

Assuming your data start on Row 1, put this formula in cell B1...

=TRIM(LEFT(A1,FIND("(",A1)-1))

and then put this formula in cell C1 and copy it across...

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1," ",""),")[","("),"](","("),")",""),"(",REPT(" ",200)),COLUMNS($A:A)*200,200))