Splitting the text into column

[Raph85]

Hello,

I have data that to be split into each different column, however the problem is if I split it using the text to column method it will split every single words to space (column) and I can do it with the fixed width either because it's different width.

How do i attach the file to show what I mean here ?

Thank you.

 

[Legacy 421546]

What is the delimiter character you want to split on?

 

[Fluff]

How do i attach the file to show what I mean here ?

You cannot upload files to the site, but there are some tools available here https://www.mrexcel.com/forum/about-board/508133-attachments.html
That will let you post data to the thread

 

[jtakw]

Hi,

You can also just post a few (not one) samples of your data (all possible variations) here.

 

[Raph85]

Hi Guys,

Thank you for the reply. Please find the sample as per below and I hope, it works:

[TABLE="width: 568"]
<tbody>[TR]
[TD="colspan: 6"]Last Name/First Title Name Confirmation# Company Name Member Status [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 6"]Smith/Rony 2454843675 Google Gold [/TD]
[/TR]
[TR]
[TD="colspan: 6"]Gaylord/John 2467917946 Silver [/TD]
[/TR]
[TR]
[TD="colspan: 6"]Heathrow/Jenny Ms 2365974598 [/TD]
[/TR]
[TR]
[TD="colspan: 6"]Jeans/Jenny 6659878854 Apple Store [/TD]
[/TR]
[TR]
[TD="colspan: 6"]Mirka/Sean 6598457832 Black [/TD]
[/TR]
[TR]
[TD="colspan: 6"]Dean/Kim 6598781245 IBM Blue [/TD]
[/TR]
[TR]
[TD="colspan: 6"]Di Silva/ Anthonio 9878456589 Silver [/TD]
[/TR]
[TR]
[TD="colspan: 6"]La Belle/Marie 1245987865 Lo Lo Hoop Pty Ltd Blue [/TD]
[/TR]
[TR]
[TD="colspan: 6"]Hillsgrove/Dorian 2356457889 La Mere De La Cruze [/TD]
[/TR]
[TR]
[TD="colspan: 6"]Brock/Joe Mr 9878456589 Gold[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Need to be split into each column as per below[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Last Name [/TD]
[TD]First Name [/TD]
[TD]Title [/TD]
[TD]Confirmation #[/TD]
[TD]Company Name[/TD]
[TD]Member Status [/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]Rony [/TD]
[TD] [/TD]
[TD="align: right"]2454843675[/TD]
[TD]Google [/TD]
[TD]Gold [/TD]
[/TR]
[TR]
[TD]Gaylord[/TD]
[TD]John[/TD]
[TD] [/TD]
[TD="align: right"]2467917946[/TD]
[TD] [/TD]
[TD]Silver[/TD]
[/TR]
[TR]
[TD]Heathrow[/TD]
[TD]Jenny[/TD]
[TD]Ms[/TD]
[TD="align: right"]2365974598[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jeans[/TD]
[TD]Jenny[/TD]
[TD] [/TD]
[TD="align: right"]6659878854[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mirka[/TD]
[TD]Sean[/TD]
[TD] [/TD]
[TD="align: right"]6598457832[/TD]
[TD] [/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]Dean[/TD]
[TD]Kim[/TD]
[TD] [/TD]
[TD="align: right"]6598781245[/TD]
[TD]IBM[/TD]
[TD]Blue [/TD]
[/TR]
[TR]
[TD]Di Silva[/TD]
[TD]Anthonio [/TD]
[TD] [/TD]
[TD="align: right"]9878456589[/TD]
[TD] [/TD]
[TD]Silver[/TD]
[/TR]
[TR]
[TD]La Belle[/TD]
[TD]Marie[/TD]
[TD] [/TD]
[TD="align: right"]1245987865[/TD]
[TD]Lo Lo Hoop Pty Ltd [/TD]
[TD]Blue [/TD]
[/TR]
[TR]
[TD]Hillsgrove[/TD]
[TD]Dorian [/TD]
[TD] [/TD]
[TD="align: right"]2356457889[/TD]
[TD]La Mere De La Cruze[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Brock [/TD]
[TD]Joe[/TD]
[TD]Mr[/TD]
[TD="align: right"]9878456589[/TD]
[TD] [/TD]
[TD]Gold[/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col><col><col></colgroup>[/TABLE]

 

[jtakw]

Since your data is Not "Uniform" in content, each Column will require it's own formula:

Book1
	A	B	C	D	E	F	G
1		Last Name	First Name	Title	Confirmation	Company Name	Member Status
2	Smith/Rony 2454843675 Google Gold	Smith	Rony		2454843675	Google	Gold
3	Gaylord/John 2467917946 Silver	Gaylord	John		2467917946		Silver
4	Heathrow/Jenny Ms 2365974598	Heathrow	Jenny	Ms	2365974598
5	Jeans/Jenny 6659878854 Apple Store	Jeans	Jenny		6659878854	Apple Store
6	Mirka/Sean 6598457832 Black	Mirka	Sean		6598457832		Black
7	Dean/Kim 6598781245 IBM Blue	Dean	Kim		6598781245	IBM	Blue
8	Di Silva/ Anthonio 9878456589 Silver	Di Silva	Anthonio		9878456589		Silver
9	La Belle/Marie 1245987865 Lo Lo Hoop Pty Ltd Blue	La Belle	Marie		1245987865	Lo Lo Hoop Pty Ltd	Blue
10	Hillsgrove/Dorian 2356457889 La Mere De La Cruze	Hillsgrove	Dorian		2356457889	La Mere De La Cruze
11	Brock/Joe Mr 9878456589 Gold	Brock	Joe	Mr	9878456589		Gold
Sheet95

Cell Formulas
Range	Formula
B2	=LEFT(A2,FIND("/",A2)-1)
C2	=TRIM(MID(A2,FIND("/",A2)+1,FIND(" ",TRIM(MID(A2,FIND("/",A2)+1,255)))))
D2	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1),"/ ",""),"/",""),B2&C2,""))
E2	=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10)
F2	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND(E2,A2)+10,255),"Gold",""),"Silver",""),"Black",""),"Blue",""))
G2	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"/ ",""),"/",""),B2&C2,""),D2,""),E2,""),F2,""))

B2:G2 formulas copied down.