How to remove characters and then split string at a specifc character

[Waimea]

I have a lot of strings that follow the same format.

[COLOR=#3D3D3D][FONT=arial]T4290 Benareby (Härryda kommun)[/FONT][/COLOR]

Steps:

1.) Remove the first 5 charcters, T4290.

=RIGHT(A2;LEN(A2)-6)

2.) Remove leading spaces from Benareby. Maybe with TRIM.

TRIM(B2)

3.) Remove ( kommun) and keep Härryda

I am stuck at step 3 right now?I have tried using REPLACE.

Desired output is

Column A   Column B
Benareby   Härryda

 

[Joe4]

For an entry in cell A1.

Here is a formula to return the first part:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))

and here is a formula to return the second part:

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("(",A1)+1,100)," ",REPT(" ",100)),100))

 

[Fluff]

How about

Book1
	A	B	C
2	T4290 Benareby (Hrryda kommun)	Benareby	Hrryda
Sheet4

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE(A2,"(",REPT(" ",100)),FIND(" ",A2),100))
C2	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100),3),FIND("(",A2)+1,100))

 

[Waimea]

Hi Joe4 and Fluff,

thank you very much for your replies! Both of your formulas work and I am happy!

Thank you again!

I don't understand the use of REPT(100) ?

 

[Fluff]

That part puts 100 spaces into the string, making it easier to get the part you need.

 

[Waimea]

Hi Fluff,

thank you for explanation, it saved me hours of manually removing the unwanted information.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Peter_SSs]

.. and just for fun, a couple of options without adding any spaces.

Excel Workbook
	A	B	C
1	T4290 Benareby (Hrryda kommun)	Benareby	Hrryda
Extract text

 

[Waimea]

Hi Peter_SSs,

thank you for your reply!

 

[Rick Rothstein]

One more... a single formula solution for both parts. Assuming your data starts in cell A1, put this formula in cell B1 and copy it to cell C1, then copy B1:C1 down to the bottom of your data (or past it if you want to provide for addition data later on)...

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"(","")," ",REPT(" ",100)),COLUMNS($B:B)*100,100))