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

 

[Waimea]

Hi Rick,

thank you for your reply. I like the single formula solution!

 

[Rick Rothstein]

Extract text

	A	B	C
1	T4290 Benareby (Härryda kommun)	Benareby	Härryda

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:230px;"><col style="width:74px;"><col style="width:83px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =TRIM(REPLACE(LEFT(A1,FIND("(",A1)-1),1,5,"")) <tbody> </tbody>

<tbody>
</tbody>

For your B1 formula, why not this instead...

=MID(LEFT(A1,FIND("(",A1)-1),7,99)

 

[Peter_SSs]

For your B1 formula, why not this instead...

=MID(LEFT(A1,FIND("(",A1)-1),7,99)

Only that the OP said they wanted to remove the first 5 characters, so I took that literally. (Though not some other parts see below. )

BTW, your suggestion would need to be -2 or perhaps safer include TRIM() or else a space is likely left on the end.

I like the single formula solution!

Just be aware that different formulas suggested in the thread may bring different results.
Having re-read your question and considered your possible data, I'm not sure any of the suggestions, including mine, would be sure to bring you your desired results. I'm proposing this instead. I am assuming that spaces after the Småortskod (hope that is correct ), before the "(" and "kommun" are uniform.

Excel Workbook
	A	B	C
1	T4290 Benareby (Hrryda kommun)	Benareby	Hrryda
2	S5133 Lekvall (Lilla Edet kommun)	Lekvall	Lilla Edet
3	S4474 Stora Brta (Hrryda kommun)	Stora Brta	Hrryda
Extract text