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

Waimea

Active Member
Joined
Jun 30, 2018
Messages
342
I have a lot of strings that follow the same format.

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

1.) Remove the first 5 charcters, T4290.
Code:
=RIGHT(A2;LEN(A2)-6)
2.) Remove leading spaces from Benareby. Maybe with TRIM.
Code:
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
Code:
Column A   Column B
Benareby   Härryda
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,513
Office Version
365
Platform
Windows
For an entry in cell A1.

Here is a formula to return the first part:
Code:
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))
and here is a formula to return the second part:
Code:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("(",A1)+1,100)," ",REPT(" ",100)),100))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
How about
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #3D3D3D;;">T4290 Benareby (Härryda kommun)</td><td style=";">Benareby</td><td style=";">Härryda</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A2,"(",REPT(<font color="Purple">" ",100</font>)</font>),FIND(<font color="Green">" ",A2</font>),100</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A2," ",REPT(<font color="Purple">" ",100</font>),3</font>),FIND(<font color="Green">"(",A2</font>)+1,100</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
342
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
That part puts 100 spaces into the string, making it easier to get the part you need.
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
342
Hi Fluff,

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,320
Office Version
365
Platform
Windows
.. and just for fun, a couple of options without adding any spaces. :)

Excel Workbook
ABC
1T4290 Benareby (Hrryda kommun)
Extract text
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
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))
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top