Split Column Data into Multiple Columns

[Mnet22]

Dear MrExcel, I have a column "J" -that contains the following data e.g.

PB GRN11284 1582800-10-07/20
SL Core Quarantine
MO17-09 NCR1259 - Rejected sent to Quarantine
MO17-09 NCR1259 - Rejected sent to Quarantine
AD B20 1575700-01-01/20

It should be split into the next three Columns -

Column G            Column H	            Column I
PB 	          GRN11284	         582800-10-07/20
SL                Core	                 Quarantine
MO17-09 	  NCR1259	         Rejected sent to Quarantine
MO17-09 	  NCR1259	         Rejected sent to Quarantine
AD 	           B20	                1575700-01-01/20

Any Help will be greatly appreciated - Thanks in Advance!!

 

[ttray33y]

text to columns function? but theres a slight problem, you have to fix the "Rejected sent to Quarantine" either thru VBA or manually it depends on the amount of data you have.

 

[Mnet22]

Thank you will try the text to column function!!

 

[FormR]

It should be split into the next three Columns -

Hi, so is the rule, the first word goes in column G, the next word in column H and the rest of the text, without any " - " in column I?

Excel 2013/2016
	G	H	I	J
2	PB	GRN11284	1582800-10-07/20	PB GRN11284 1582800-10-07/20
3	SL	Core	Quarantine	SL Core Quarantine
4	MO17-09	NCR1259	Rejected sent to Quarantine	MO17-09 NCR1259 - Rejected sent to Quarantine
5	MO17-09	NCR1259	Rejected sent to Quarantine	MO17-09 NCR1259 - Rejected sent to Quarantine
6	AD	B20	1575700-01-01/20	AD B20 1575700-01-01/20
Sheet1

Cell Formulas
Range	Formula
G2	=LEFT(J2,FIND(" ",J2)-1)
H2	=TRIM(LEFT(SUBSTITUTE(MID(J2,FIND(" ",J2)+1,255)," ",REPT(" ",255)),255))
I2	=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(J2," - "," ")," ",REPT(" ",255),2),255))

 

[Mnet22]

Dear FormR - exactly what I needed it is working perfectly!! Thank you for all the help, time and effort it is greatly appreciated!! Thank You!!

 

[Rick Rothstein]

Dear MrExcel, I have a column "J" -that contains the following data e.g.

PB GRN11284 1582800-10-07/20
SL Core Quarantine
MO17-09 NCR1259 - Rejected sent to Quarantine
MO17-09 NCR1259 - Rejected sent to Quarantine
AD B20 1575700-01-01/20

It should be split into the next three Columns -

Column G            Column H	            Column I
PB 	          GRN11284	         582800-10-07/20
SL                Core	                 Quarantine
MO17-09 	  NCR1259	         Rejected sent to Quarantine
MO17-09 	  NCR1259	         Rejected sent to Quarantine
AD 	           B20	                1575700-01-01/20

Any Help will be greatly appreciated - Thanks in Advance!!

I know you have a formula solution that you appear to be happy with, but I thought I would post a macro solution for you and future readers of this thread to consider. Your data does not seem to be the kind that will change once placed in the cells of Column J, so it does not seem to need the live update-ability that formulas provide, hence, a macro solution which does not litter up the worksheet with formulas seemed like it might by useful. Here is the macro which assumes the data starts on Row 2 (change the red 2's if that is a wrong guess)...

[table="width: 500"]
[tr]
	[td]Sub SplitColumnJintoColumnsGandHandI()
  Dim R As Long, Data As Variant, Result As Variant, Parts() As String
  Data = Range("J2", Cells(Rows.Count, "J").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 3)
  For R = 1 To UBound(Data)
    Parts = Split(Data(R, 1), " ", 3)
    Result(R, 1) = Parts(0)
    Result(R, 2) = Parts(1)
    Result(R, 3) = Trim(Replace(Parts(2), "-", ""))
  Next
  Range("G2:I" & UBound(Result) + 1) = Result
End Sub[/td]
[/tr]
[/table]

 

[Mnet22]

Great work...Thank you Rick!!!