LinkBack URL
About LinkBacks
I have data in column A which has to be split to respective 4 columns
In column B I used the left formula , but looking formula to split data in column B , C and D
22011011 MINERAL WATER FR 1x10x1L Total
B C D E
22011011 MINERAL WATER 1x10x1L FR
I have data in column A which has to be split to respective 4 columns
In column B I used the left formula , but looking formula to split data in column B , C and D
22011011 MINERAL WATER FR 1x10x1L Total
B C D E
22011011 MINERAL WATER 1x10x1L FR
Depending on your usage, I feel that flash fill would be your best choice for splitting the data into separate columns.
Your main issue with any other approach (i.e. Text to Columns/forumlae/VBA) is the varied length of strings you want to return, i.e. 'Prepared Cereals VS Cereals'.
For example, for 'Header 2' I would probably use something like the below:
=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1))
However, for 'Prepared Cereals' you would only get the result: 'Prepared'.
Flash fill, however, would learn what you are trying to do, and return 'Prepared Cereals'.
The below office support article contains many instances of this, and may prove useful to you:
https://support.office.com/en-gb/art...a-50dded6e4a68
C2:
=TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," Total",""),B2,""),D2,""))," "&E2,""))
D2:
=IF(LEFT(RIGHT(TRIM(SUBSTITUTE(A2," Total","")),3),1)=" ","",TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A2," Total",""))," ",REPT(" ",100)),100)))
E2:
=RIGHT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," Total",""),B2,""),D2,"")),2)
The formula in C will work even if the nationality letters (e.g. FR) are present in the product descriptor (e.g. "refrozen widgets"
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Enter formula in B2 and copy across and down
Formula:
Please Login or Register to view this content.
v A B C D E 1 TARIFF MERGE HEADER_1 HEADER_2 HEADER_3 HEADER_4 2 19041000 CEREALS FR Total 19041000 CEREALS FR 3 19042000 PREPARED CEREALS ZA Total 19042000 PREPARED CEREALS ZA 4 21069060 SYRUP FR 6x9x1L Total 21069060 SYRUP FR 6x9x1L 5 21069090 FOOD PREP. FR Total 21069090 FOOD PREP. FR 6 22011011 MINERAL WATER FR 1x10x1L Total 22011011 MINERAL WATER FR 1x10x1L 7 22087090 LIQUEURS FR 6x7x1L Total 22087090 LIQUEURS FR 6x7x1L 8 22087090 LIQUEURS FR 12x8x70CL Total 22087090 LIQUEURS FR 12x8x70CL 9 22087090 LIQUEURS IE 6x8x70CL Total 22087090 LIQUEURS IE 6x8x70CL 10 22011011 MINERAL WATER FR 9x6x1.5L Total 22011011 MINERAL WATER FR 9x6x1.5L
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
@Alkey
Thanks for solution, testing on a range of data gave me some unexpected results in column C and D.
Could you look in.
Also I notice in formula as below the origin is hard coded , is there another way because sometimes I have more than 30 to 40 origin.
Please Login or Register to view this content.
If you can update the list of origin, it'll will great to resolve it
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
See attached complete list codes in column A , hope it helps
Glenn's reply in post #3 is independent of the country codes i.e. table not required.
Re-attached Glenn's solution with "additional" codes
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks