splitting one Column into many other columns

[wali]

Hello,
I need a macro which place chosen parts from one column into another column.

For example I have following text in column E:

Column E
Flugschneise {f}
ringsum
(total) im Arsch [vulg.]
mutterseelenallein
Geld und Gut
Pensum {n}
totgelacht
*Schlotterkamm {m} [Ab- und Umbau des Kieferkamms]
Weißkopfseeadler {m}
Rabengeier {m}
Pfeilschwanzkrebs {m}
Indianer {m}
Indianerin {f}
Königsgeier {m}
Carolinataube {f}
Trauertaube {f}
Zwergsultanshuhn {n}
Amerikanischer Spitzmull {m}
Schwalbenweih {m}
Regenmenge {f}
Sachverstand {m}
Und damit basta! [ugs.]
(Und) damit hat sich der Fall.
(Und) damit ist die Sache erledigt.

*chosen content not always at the end of a cell!!

Now i need a macro which puts chosen content from Column E to Column F or Column G. For example I want that all “{f}s” should be deleted from column E and put into column E. Or I want all {n} to be deleted and placed into column F. Or I want all [vulg.] to be deleted from column E and put into column G. If column F or G already consist of text then the new text( {f} or {n} or [vulg.] ...) should be added to the existing text but separated through a separator like comma or semicolon …

It means that the macro is each time fed with information about what to delete from which column and where to put it. It would be cool if a simple msg box would ask for the needed information.

I hope that the problem is described understandable/precise by me. If not, please let me know.

Thanks in advance for every answer. And thanks a lot for commenting the solution/code if any as much as possible! :-)

Wali

[solnajeff]

Hi

Before I make a reply could you please post an example worksheet of what is in the destination cells and how you want it to be joined / added as it is difficult to know exactly what you want.

Regards

Jeff

[jindon]

For example I want that all “{f}s” should be deleted from column E and put into column E.

1) Delete from Col.E and put into Col.E ?
2) what should be deleted and moved, whole data or just {f} ,{n}, etc?

Can you just show us your desired result?

[wali]

Hi,
sorry it was a typing mistake. what i meant was that "{f}s" should be deleted from column E and put into column F. I attached an excel file and hope that it explains the problem better.

I have a word list containing many thousand of words. Now only one macro is needed which splits the wordlist into many. According to the example in Excel file imagine i told the macro to replace {f}s from column E to column F. The same macro was run again but this time it was asked to to replace {n}s from E to F. Third time it was asked to do that with {m}. Forth time [schule] from E to G....

It means the same macro is run many times till the data is put into those cells where the user want them to be. Every time before running the macro, the user should be asked " what to replace, from which column to which column?".

In the attached excle example i have used many sheets to demonstrate the steps. But in real the macro should do the work in only one sheet. More sheets are not needed

[jindon]

wali

Sorry, but I can only see the attachment with excel or picture formated files.

[wali]

Hi,
thank for your quick answer.
unfortunately i cant attach excel file here. But i uploaded it for you and here is the external link where you can download the excel file:

splitting one column1.xls

[jindon]

OK great.

Can it be said that in the result sheet;

Other than those are in Col.B Col.C, Col.D & Col.F, goes to Col.G or H ?

anyway I got incredibly slow connection today and I must go now.
I'll post the code tomorrow.

[wali]

Hi,
sorry but i cant understand your question. There is only one sheet and it should remain the one sheet. Sorry if it is still not clear. I will give it another try:

Please Login or Register  to view this content.

Now i need a macro which replaces (delets from one Column and writes into another column) selected part from one chosen col. to another one. It means the macro replaces only one given group of charactors and it needs information what to replace and from where to where each time. Lets say accourding to our example i want the macro to delet all "lau"s from column E and put them into column Z.

Please Login or Register  to view this content.

and if i want the same macro to replace "au" from col. Z to Colmn F it should do it:

Please Login or Register  to view this content.

Each time i run the macro it should ask me:

What to replace=""?
Replace from Column=""?
Write into Column=""?

The answer to macros question for information accoriding to our upper example could be:
First for Lau:
What to replace="lau"?
Replace from Column="E"?
Write into Column="Z"?

if i want to run the macro again for "au":
What to replace="au"?
Replace from Column="Z"?
Write into Column="F"?

[jindon]

OK
Just the data for Col.E
assuming
everything {x} will be in Col.F
everything [x] will be in col.G or H

Please Login or Register  to view this content.

Regading your last message, it looks like totally different question from your originla question.

[wali]

Thank you very much.
When i start the macro it shows me an error in the line:

Please Login or Register  to view this content.

What am i doing wrong?

[jindon]

Thank you very much.
When i start the macro it shows me an error in the line:

Please Login or Register  to view this content.

What am i doing wrong?

Can you just change that line to

Please Login or Register  to view this content.

As I have to go now and I'll be busy tomorrow, if any inconvenience, I'll post after tomorrow.

Is the code I posted for your dictionay working by the way ?

[wali]

Hi,
thank you very much. Now it works but unfortunately for small number of words. When i put lots of words then i shows me "runtime error 13". I want to process about 300 000 words with it(excel 2007 German & excel 2003 German Ver). But the problem is that the macro cant even handel 3000 words. As soon is the number of rows is over 2000 it shows runtime error 13.

[wali]

here is an example file where it doesnt work.

5000 words

[jindon]

try

Please Login or Register  to view this content.

[wali]

Hi Jindon,
thank you very much. Unfortunately i cant run the macro cus it shows me " Runtime error 424".

I have a computer "Intel 4 2,8 Ghz, 1 GB RAM, Windows XP, Office 2003 and Office 2007". If more RAM helps, i will buy it tomorrow.

[jindon]

OOPs

Can you just delete the line of

Please Login or Register  to view this content.

[wali]

Thanks Jindon. The problem is solved. I tried the macro on very very big wordlist. And it worked. Thanks alot