Separate text from text field to individual columns

alegall

New Member
Joined
Jun 19, 2015
Messages
3
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:

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

This is what I am trying to accomplish:
ABCDE
How to Setup External Telnet or SSH Client ( 2 ) [Wiki] (21)How to Setup External Telnet or SSH Client2Wiki21

<tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

shawnhet

Well-known Member
Joined
Feb 12, 2011
Messages
547
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

New Member
Joined
Jun 19, 2015
Messages
3
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

Board Regular
Joined
Mar 23, 2015
Messages
144
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
 
Last edited:

alegall

New Member
Joined
Jun 19, 2015
Messages
3

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,918
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,292
Messages
5,600,765
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top