a macro that separates - to find a text from a list (column L) in a cell in (column B) and cut and paste it in (column D and column E)

[SOCII]

Hi ,
i am a new starter with macros and need some help urgently.
i am trying to separate a cell ( column B - range xxxx) into 2 columns ( D &E ) based on a list ( column L - range xxxx). All this with a loop.

can anyone help.
much appreciated ,
thank in advance

 

[Joe4]

I see it appears that you want to separate the first two terms from column B into columns D or E.
That can be accomplished pretty easily with normal Excel formulas or VBA.
But I fail to see what role column L plays in this?
Can you explain that in more detail?

 

[SOCII]

thanks for your reply.
to be clear , I don't want to separate the first 2 terms . what I am trying to achieve is to separate
1. the items in column B that are in the list ( colum L ) into colum E and
2. then the remaing text into column D

 

[SOCII]

abd to be more precise
1. the text charaters are not necessarily same lenghts in colum B - this is why i need to define a list ( column L) and then try to extract them , the list can becomprised of numbers , text of different lenghts as well
2. i have > 1000 rows of information

thanks

 

[Joe4]

thanks for your reply.
to be clear , I don't want to separate the first 2 terms . what I am trying to achieve is to separate
1. the items in column B that are in the list ( colum L ) into colum E and
2. then the remaing text into column D

I am afraid that does not really clarify anything for me.

Please walk me through an example, step-by-step, explaining the process along the way.

And please do NOT oversimplify the data or example for sake of posting it here!
More people get into trouble here when they post an oversimplified example of their issue here, and then they get an answer which works perfectly on their oversimplified example, but does not work on their actual data (note that you do NOT need to post a lot of data, but what you do post should be an accurate depiction of the type of data you are working with).

 

[SOCII]

ok
i have in column B name address post codes etc...
i need to separate
a. post codes
b. countries names
c address

1. I will have to define a list of Post Codes ( Column L , various lenghts......) and then try and extract from each line in column B the Post Codes from the list and paste in column E

2. ideally I need to create a list of Countries and do the same ( say in column F) and extract then as well ( this is the same step as for Post Codes , which I had not included in my initial request)

3. and the remaining text etc... to be pasted in column D ( which should be rest of address)

thanks

 

[Joe4]

Can you post a sampling of a handful of realistic looking values that you will have in column B?
These things can get really tricky, as countries and addresses can have any number of words in them.
And if there are any "soft carriage returns" in them, that may be helpful.
So we really need a realistic picture of what your data actually looks like (and not some dummied-down example).

The important thing to remember with Excel is that it cannot "think" for itself - it is very literal. It can only do what you tell it to.
So you have to supply the exact logic/rules it needs to use to determine these things.
Usually, if on paper you can come up with the list of rules it needs to follow to parse the data, and it works for ANY and ALL your rows of data, we can program to that.
However, if you have too many exceptions, and cannot come up with hard-and-fast rules that will work on all data, then you are going to have a problem (you cannot program that which you cannot conceive).

In many cases, when dealing with things like names and addresses, people often have to settle with coming up with a solution that works on 90-95% of their data, and then realize they need to review it to fix all the exceptions manually.

 

[JEC]

You could try this, based on your sample picture in post 1. Without the need of VBA

In E5:

=LOOKUP(100,SEARCH($L$5:$L$9,B5),$L$5:$L$9)

IN D5:

=IF(E5<>"",TRIM(SUBSTITUTE(B5,E5,"")),"")

 

[JEC]

Map1
	B	C	D	E	F	G	H	I	J	K	L
4	Tekst Cell		Destination cells								Post code
5	xxxx 92150 ddddd		xxxx ddddd	92150							75000
6	xxxx 92000 ddddd		xxxx ddddd	92000							90000
7	xxxx 75000 ddddd		xxxx ddddd	75000							92000
8	xxxx 93000 ddddd		xxxx ddddd	93000							92150
9	xxxx 93000 ddddd		xxxx ddddd	93000							93000
10	xxxx 90000 ddddd		xxxx ddddd	90000
11	xxxx 75000 ddddd		xxxx ddddd	75000
Blad1

Cell Formulas
Range	Formula
D5:D11	D5	=IF(E5<>"",TRIM(SUBSTITUTE(B5,E5,"")),"")
E5:E11	E5	=LOOKUP(100,SEARCH($L$5:$L$9,B5),$L$5:$L$9)