How to separate text out to a new column - Text to Column does not work

S

shortjake

New Member
Joined
Jun 29, 2016
Messages
21
Hi guys,

I have a column with multiple rows of text in each cell. There is a point "Specifications" where I need the text data to be separated out into a new column. The point at which the data needs to be separated is at the word Specifications.

I tried to Find > Replace > Specifications with ; Specifications. (inserted a delimiter of ; before every word of Specifications) and then clicked text to column function using the ; as the delimiter. This did not work and instead, it just separated out the first line of text in each cell which was "ACCOSSATO - race-developed for high performance" rather than going to find the delimiter and separating via that.

Please help :)

update for spec seperation.xlsx
NO
1DescriptionSpecification
2ACCOSSATO - race-developed for high performance Fixed Rear Steel Brake Disc for Honda and Triumph models Interchangeable with the original rear brake disc Includes single disc only ACCOSSATO - race-developed for high performance
3ACCOSSATO - race-developed for high performance Fixed Rear Steel Brake Disc for BMW and Husqvarna models Interchangeable with the original rear brake disc Includes single disc only ;Specifications Outer diameter 265mm Inner diameter 136.5mm Thickness  5mm Weight TBA Part no: DK004 Model compatibility Husqvarna Nuda 900 / R 2011 - 2011 BMW K1300R 2008 - 2013 BMW K1300S 2015 - 2015 BMW K1300S 2009 - 2012 BMW R nine T 2014 - 2014 BMW R1200ST 2005 - 2008 BMW R1200S 2006 - 2008 BMW R1200RT 2005 - 2012 BMW R1200R 2006 - 2012 BMW R1200GS / ABS 2004 - 2012 BMW R1200GS Adventure 2006 - 2012 BMW K1200R 2005 - 2008 BMW K1200S 2005 - 2007 BMW HP2 Sport 2008 - 2011 BMW HP2 Megamoto 2007 - 2010 BMW HP2 Enduro 2006 - 2009 BMW S1000XR 2015 - 2015 BMW F800ST 2006 - 2012 BMW F800S 2006 - 2010 BMW F800R 2009 - 2011 BMW F800GT 2013 - 2013 BMW F800GS Adventure 2014 - 2014 BMW F800GS 2009 - 2009 BMW F700GS 2013 - 2013 BMW F650GS 2008 - 2008ACCOSSATO - race-developed for high performance
4ACCOSSATO - race-developed for high performance Fixed Rear Steel Brake Disc for BMW R1150 R1100 R850 Interchangeable with the original rear brake disc Includes single disc only ;Specifications Outer diameter 276mm Inner diameter 176mm Thickness  5mm Weight 0.75Kg Part no: DK005 Model compatibility BMW R1150RT / ABS 2001 - 2005 BMW R1150RS 2001 - 2005 BMW R1150R Rockster 2003 - 2006 BMW R1150R 2001 - 2006 BMW R1150GS Adventure 2002 - 2005 BMW R1150GS 1999 - 2004 BMW R1100S / Boxer Cup 1998 - 2006 BMW R1100RT / ABS 1994 - 2001 BMW R1100RS 1985 - 1993 BMW R1100R / Roadster 1995 - 2001 BMW R1100GS 1994 - 2001 BMW R100R 1991 - 1996 BMW R850RT / ABS 1995 - 2004 BMW R850 Roadster 1995 - 2000 BMW R850R 1994 - 2007 BMW R850GS 1998 - 2007 BMW R850C 1998 - 2001ACCOSSATO - race-developed for high performance Fixed Rear Steel Brake Disc for BMW R1150 R1100 R850
5ACCOSSATO - race-developed for high performance Fixed Front Steel Brake Disc for BMW R1200 K1200 HP2 Enduro R1150 R1100 Interchangeable with the original front brake discs Includes single disc only Must be fitted as a pair ;Specifications Outer diameter 305mm Inner diameter 181mm Thickness  5mm Weight 1.2Kg Part no: DK006 Model compatibility BMW R1200GS Adventure 2006 - 2007 BMW R1200 Cruiser 1997 - 2001 BMW R1200CL 2002 - 2006 BMW R1200C Classic 2003 - 2003 BMW R1200C 1997 - 2001 BMW K1200RS 1997 - 2000 BMW K1200LT 1999 - 2001 BMW HP2 Enduro 2006 - 2009 BMW R1150GS Adventure 2002 - 2005 BMW R1150GS 1999 - 2004 BMW R1100S 1998 - 2000 BMW R1100GS 1994 - 2001 BMW R850R 2002 - 2007 BMW R850GS 1998 - 2007 BMW R850C 1998 - 2001ACCOSSATO - race-developed for high performance
6ACCOSSATO - race-developed for high performance Fixed Front Steel Brake Disc for BMW R1200 K1200 HP2 Megamoto R1150 Interchangeable with the original front brake discs Includes single disc only Must be fitted as a pair ;Specifications Outer diameter 320mm Inner diameter 181mm Thickness  5mm Weight 1.1Kg Part no: DK007 Model compatibility BMW R1200ST 2005 - 2007 BMW R1200S 2006 - 2007 BMW R1200RT 2005 - 2007 BMW R1200R 2006 - 2007 BMW K1200S / ABS 2005 - 2007 BMW K1200R Sport 2007 - 2007 BMW K1200RS 2001 - 2006 BMW K1200R 2005 - 2008 BMW K1200LT 2002 - 2002 BMW K1200GT 2002 - 2007 BMW HP2 Megamoto 2007 - 2007 BMW R1150RT / ABS 2001 - 2005 BMW R1150RS 2001 - 2005 BMW R1150R Rockster 2003 - 2006 BMW R1150R 2001 - 2006 BMW R1100S Boxer Cup 2003 - 2006 BMW R1100S 2001 - 2006 BMW R850RT 2002 - 2004 BMW F800ST 2006 - 2007 BMW F800S 2006 - 2007ACCOSSATO - race-developed for high performance
7ACCOSSATO - race-developed for high performance Fixed Rear Steel Brake Disc for Ducati and Yamaha models Interchangeable with the original rear brake disc Includes single disc only ;Specifications Outer diameter 245mm Inner diameter 115mm Thickness  4mm Weight 0.7Kg Part no: DK008 Model compatibility Yamaha YZF-R1 1998 - 2001 Yamaha TDM 900 / ABS 2002 - 2010 Yamaha YZF-R750 1993 - 1997 Yamaha XJN 600 1998 - 2003 Yamaha XJR 400 1995 - 2000 Ducati ST3 / ABS 2004 - 2007 Ducati Sport 1000 / S 2006 - 2008 Ducati Paul Smart 1000 2006 - 2007 Ducati Monster S2R / Dark 2006 - 2007 Ducati Monster 1000S IE 2003 - 2005 Ducati GT 1000 2006 - 2010 Ducati 1000 SS 2003 - 2006 Ducati ST4 / S 1998 - 2005 Ducati ST2 1997 - 2003 Ducati Monster S4 2001 - 2003 Ducati 907 IE 1990 - 1993 Ducati SuperLight 900 1992 - 1997 Ducati 900 Sport 2002 - 2002 Ducati Monster 900 / IE 1993 - 2002 Ducati 900 SuperSport / IE 1989 - 2002 Ducati 899 Panigale 2014 - 2014 Ducati 888 SP / Strada 1990 - 1995 Ducati 851 1989 - 1993 Ducati Monster 821 2015 - 2015 Ducati 800 SuperSport 2003 - 2006 Ducati Scrambler 800 2015 - 2015 Ducati Monster 800S IE 2003 - 2004 Ducati 800 Sport 2003 - 2004 Ducati Monster 795 2012 - 2014 Ducati Monster 750 / IE 1994 - 2002 Ducati 750 SuperSport / IE 1991 - 2002 Ducati 750 Sport 2001 - 2002 Ducati Monster 696 / ABS 2008 - 2013 Ducati Monster 695 2006 - 2007 Ducati 620 SuperSport 2002 - 2005 Ducati 620 Sport 2003 - 2004 Ducati Multistrada 620 2004 - 2006 Ducati Monster 620 / IE 2002 - 2006 Ducati Monster 600 1993 - 2002 Ducati 600 SuperSport 1991 - 2000 Ducati Monster 400 / IE 2001 - 2006 Ducati Monster 400 1995 - 1997 Ducati 400 SuperSport 1993 - 1997 Ducati 350 SuperSport 1991 - 1992ACCOSSATO - race-developed for high performance
8ACCOSSATO - race-developed for high performance Fixed Front Steel Brake Disc for Honda CBR650F CB650F CBR500R CB500F CB500X Interchangeable with the original front brake disc(s) Includes single disc only Must be fitted as a pair (single front disc models excepted) ;Specifications Outer diameter 320mm Inner diameter 210mm Thickness  5mm Weight 0.95Kg Part no: DK230N Model compatibility Honda CBR650F / L / ABS 2014 - 2018 Honda CB650F / L / ABS 2014 - 2018 Honda CBR500R 2013 - 2021 Honda CB500X / ABS 2013 - 2019 Honda CB500F / ABS 2013 - 2019ACCOSSATO - race-developed for high performance
9ACCOSSATO - race-developed for high performance Fixed Front Steel Brake Disc for Kawasaki ZX-10R ZX-6R ZX-14/R 1400GTR Z800 Interchangeable with the original brake discs Includes single disc only Must be fitted as a pair ;Specifications Outer diameter 310mm Inner diameter 80mm Thickness  5mm Weight 1.35Kg Part no: DK235N Model compatibility Kawasaki ZX-10R 2008 - 2015 Kawasaki ZX-6R 2013 - 2021 Kawasaki ZX-14 / R 2006 - 2019 Kawasaki 1400GTR 2007 - 2017 Kawasaki Z800 2013 - 2016ACCOSSATO - race-developed for high performance
10ACCOSSATO - race-developed for high performance Fixed Front Steel Brake Disc for Kawasaki Z900 Z650 Ninja 650 Interchangeable with the original brake discs Includes single disc only Must be fitted as a pair ;Specifications Outer diameter 300mm Inner diameter 142mm Thickness  5mm Weight TBA Part no: DK236N Model compatibility Kawasaki Z900 / RS / Cafe 2017 - 2019 Kawasaki Z650 2017 - 2021 Kawasaki Ninja 650 2017 - 2021ACCOSSATO - race-developed for high performance
11ACCOSSATO - race-developed for high performance ELITE Line Fixed Front Steel Brake Disc for Kawasaki Ninja 400 Z400 Interchangeable with the original front brake disc Includes single disc only ;Specifications Outer diameter 310mm Inner diameter 142mm Thickness  5mm Weight 1.3Kg Part no: DK237N Model compatibility Kawasaki Ninja 400 2018 - 2020 Kawasaki Z400 2019 - 2020ACCOSSATO - race-developed for high performance
12ACCOSSATO - race-developed for high performance ELITE Line Floating Front Steel Brake Disc for Yamaha MT-09 XSR900 Tracer YZF-R3 MT-03 Interchangeable with the original front brake disc(s) Includes single disc only Must be fitted as a pair (single front disc models excepted) ;Specifications Outer diameter 298mm Inner diameter 132mm Thickness  5mm Weight 1.35Kg Part no: DK249N Model compatibility Yamaha MT-09 / SP 2014 - 2020 Yamaha MT-09 Tracer / GT 2015 - 2020 Yamaha Niken / GT 2018 - 2019 Yamaha XSR900 2016 - 2020 Yamaha MT-03 2006 - 2014 Yamaha YZF-R6 2003 - 2004 Yamaha XJ6 2009 - 2016 Yamaha FZ6N / S 2004 - 2011 Yamaha YZF-R3 2015 - 2019 Yamaha MT-03 2016 - 2019ACCOSSATO - race-developed for high performance
13ACCOSSATO - race-developed for high performance ELITE Line Floating Front Steel Brake Disc for Suzuki GSX-R 1000 / R Interchangeable with the original front brake discs Includes single disc only Must be fitted as a pair ;Specifications Outer diameter 320mm Inner diameter 102.2mm Thickness  5.5mm Weight 1.6Kg Part no: DKS019N Model compatibility Suzuki GSX-R 1000 2017 - current Suzuki GSX-R 1000R 2017 - currentACCOSSATO - race-developed for high performance
14ACCOSSATO - race-developed for high performance ELITE Line Floating Front Steel Brake Disc for Aprilia RSV4 Tuono V4 Interchangeable with the original front brake discs Includes single disc only Must be fitted as a pair ;Specifications Outer diameter 330mm Inner diameter 64mm Thickness  5.5mm Weight 1.55Kg Part no: DKS032N Model compatibility Aprilia RSV4 RR 2017 - 2020 Aprilia RSV4 1100 RR 2021 - current Aprilia RSV4 RF 2017 - 2018 Aprilia RSV4 1100 Factory 2019 - current Aprilia Tuono V4 1100 RR 2017 - current Aprilia Tuono V4 1100 Factory 2017 - currentACCOSSATO - race-developed for high performance
15ACCOSSATO - race-developed for high performance ELITE Line Floating Front Steel Brake Disc for Ducati Panigale Streetfighter 1198 1098 Interchangeable with the original front brake discs Includes single disc only Must be fitted as a pair ;Specifications Outer diameter 330mm Inner diameter 72mm Thickness  5.5mm Weight  1.65Kg Part no: DKS036N Model compatibility / OEM part no. Panigale V4 / S / S Corse / Speciale / R / Superleggera 2018 - 2020 OEM 49240891A (L) 49240901A (R) Streetfighter V4 S 2020 - current OEM 49240891A (L) 49240901A (R) Panigale / S / R / R FE /Superleggera 2012 - 2019 OEM 49240891A (L) 49240901A (R) Streetfighter S 2009 - 2013 OEM 49240891A (L) 49241021A (R) 1198 / S / R 2009 - 2011 OEM 49240891A (L) 49241021A (R) 1098 / S / R 2007 - 2009 OEMACCOSSATO - race-developed for high performance
16ACCOSSATO - race-developed for high performance 'ELITE Line' Floating Front Steel Brake Disc for Honda CBR1000RR Interchangeable with the original front brake discs Includes single disc only Must be fitted as a pair ;Specifications Outer diameter 320mm Inner diameter 94mm Thickness  5.5mm Weight  1.6Kg Part no: DKS038N Model compatibility / OEM part no. Honda CBR1000RR 2017 - 2019 OEM 45120-MKF-D41 / 45220-MKF-D41ACCOSSATO - race-developed for high performance
17ACCOSSATO - race-developed for high performance 'ELITE Line' Floating Front Steel Brake Disc for Honda CBR1000RR SP CBR1000RR-R SP Interchangeable with the original front brake discs Includes single disc only Must be fitted as a pair ;Specifications Outer diameter 330mm Inner diameter 94mm Thickness  5.5mm Weight  1.55Kg Part no: DKS040N Model compatibility / OEM part no. Honda CBR1000RR SP 2017 - 2019 OEM 45120-MKR-D11 / 45220-MKR-D11 Honda CBR1000RR-R SP 2020 - current OEM 45120-MKR-D11 / 45220-MKR-D11ACCOSSATO - race-developed for high performance
18ACCOSSATO - race-developed for high performance 'ELITE Line' Floating Front Brake Disc for Kawasaki ZX-10R ZX-10RR Interchangeable with the original front brake discs Includes single disc only Must be fitted as a pair ;Specifications Outer diameter 330mm Inner diameter 80mm Thickness  5.5mm Weight  1.75Kg Part no: DKS041N Model compatibility / OEM part no. Kawasaki ZX-10R 2017 - 2020 OEM 41080-0648-18U Kawasaki ZX-10R KRT 2016 - 2020 OEM 41080-0648-18U Kawasaki ZX-10RR 2019 - 2020 OEM 41080-0648-18UACCOSSATO - race-developed for high performance
Sheet1
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
It works for me. Did you try inserting the delimiter AFTER the word specifications? For example:

Find:
Specifications

Replace with:
Specifications;
 
Upvote 0
Burrgogi said:
It works for me. Did you try inserting the delimiter AFTER the word specifications? For example:

Find:
Specifications

Replace with:
Specifications;
Click to expand...
Hi Mate, Yes I did try that..... See screen shot below. It just takes out the top line of text and puts in a new column.... It soes not search for the ; and separated from that point
 

Attachments

  • Screen Shot 2022-09-19 at 1.36.26 pm.png
    Screen Shot 2022-09-19 at 1.36.26 pm.png
    155 KB · Views: 7
Upvote 0
I think I spotted the problem. Text to columns feature works only if you have one long continuous string of text or data that you're trying to separate out. In the sample you've provided in the thread, it looks like each line of text is on it's own separate line separated by a hard carriage return. Am I right? If so, that's why you're not achieving the result you want.

ACCOSSATO - race-developed for high performance

Fixed Rear Steel Brake Disc for BMW and Husqvarna models

Interchangeable with the original rear brake disc
Includes single disc only

;Specifications

Outer diameter 265mm
Inner diameter 136.5mm
Thickness  5mm
Weight TBA

Part no: DK004

Model compatibility

Husqvarna Nuda 900 / R 2011 - 2011
BMW K1300R 2008 - 2013
BMW K1300S 2015 - 2015
BMW K1300S 2009 - 2012
BMW R nine T 2014 - 2014
BMW R1200ST 2005 - 2008
BMW R1200S 2006 - 2008
BMW R1200RT 2005 - 2012
BMW R1200R 2006 - 2012
BMW R1200GS / ABS 2004 - 2012
BMW R1200GS Adventure 2006 - 2012
BMW K1200R 2005 - 2008
BMW K1200S 2005 - 2007
BMW HP2 Sport 2008 - 2011
BMW HP2 Megamoto 2007 - 2010
BMW HP2 Enduro 2006 - 2009
BMW S1000XR 2015 - 2015
BMW F800ST 2006 - 2012
BMW F800S 2006 - 2010
BMW F800R 2009 - 2011
BMW F800GT 2013 - 2013
BMW F800GS Adventure 2014 - 2014
BMW F800GS 2009 - 2009
BMW F700GS 2013 - 2013
BMW F650GS 2008 - 2008
Click to expand...
 
Upvote 0
Hi Jake,

Try this on a copy of your file. Assumes that a semicolon always precedes Specifications, and that you want them removed from column A after they're moved to column B.

VBA Code: 
Option Explicit
Sub Move_Specs()
    Dim ws As Worksheet, rng As Range, ArrIn, ArrOut, i As Long
    Set ws = Worksheets("Sheet1")   '<<<~~ Change sheet name to suit
    Set rng = ws.Range("A2", Cells(Rows.Count, "A").End(xlUp))
    ArrIn = rng
    ReDim ArrOut(1 To UBound(ArrIn, 1), 1 To 1)
  
    For i = 1 To UBound(ArrIn, 1)
        If InStr(1, ArrIn(i, 1), ";") > 0 Then
           ArrOut(i, 1) = Mid(ArrIn(i, 1), InStr(1, ArrIn(i, 1), ";") + 1, Len(ArrIn(i, 1)))
        End If
    Next i
    ws.Cells(2, 2).Resize(UBound(ArrOut, 1)).Value = ArrOut
  
    rng.Replace ";*", ""
  
End Sub
 
Upvote 0
Solution
kevin9999 said:
Hi Jake,

Try this on a copy of your file. Assumes that a semicolon always precedes Specifications, and that you want them removed from column A after they're moved to column B.

VBA Code: 
Option Explicit
Sub Move_Specs()
    Dim ws As Worksheet, rng As Range, ArrIn, ArrOut, i As Long
    Set ws = Worksheets("Sheet1")   '<<<~~ Change sheet name to suit
    Set rng = ws.Range("A2", Cells(Rows.Count, "A").End(xlUp))
    ArrIn = rng
    ReDim ArrOut(1 To UBound(ArrIn, 1), 1 To 1)
 
    For i = 1 To UBound(ArrIn, 1)
        If InStr(1, ArrIn(i, 1), ";") > 0 Then
           ArrOut(i, 1) = Mid(ArrIn(i, 1), InStr(1, ArrIn(i, 1), ";") + 1, Len(ArrIn(i, 1)))
        End If
    Next i
    ws.Cells(2, 2).Resize(UBound(ArrOut, 1)).Value = ArrOut
 
    rng.Replace ";*", ""
 
End Sub
Click to expand...
You're the man Kev. Thanks again. Worked Perfect...... note: only press the play button once, if you don't the second column disappears.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,432
Messages
6,119,468
Members
448,900
Latest member
Fairooza

Share this page

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
Back
Top