Split String into many columns according to Format criteria

**wali** · 05-19-2010, 05:23 PM

Hello everyone,
I have about 100 000 rows containing dictionary data (pashto-english). I have to split each string into many columns according to their format.

I am attaching an Excel 2007 file containing example data. Worksheet “is” contains real data as it is now and worksheet “should be” contains how it should look like after. In 2nd Row of the ws “should be” I have entered the criteria according to which the strings are to be broken.
It’s very very important that I find solutions for this. I hope that someone can help me out with it.

Many thanks in advance!

**pike** · 05-20-2010, 06:40 AM

wow wali I dont think that could be done easly
how did you enter the data ?

**wali** · 05-20-2010, 07:09 AM

I copy and pasted the data from MS Word. I could bring it as far that each cell now contains one word and its whole meanings.
I hope to get a solution. If not i would have to do at least 0.5 million times cut and paste. :-(

**pike** · 05-20-2010, 07:13 AM

even using reg exp these no real pattern
have you tried text to columns to break up the lines?

**wali** · 05-20-2010, 07:32 AM

Yes, i tried that. But it didn't work properly because for that method i need a seperator as letter. And i dont have clear seperators through which i could break the string. What i have instead is the formating, font, color and size.

I was successful as far through text into columns that I could devide the strings into three columns. Its better than nothing but still its not complete. I am attaching the example:

**pike** · 05-21-2010, 03:38 AM

hey wali
had a good think and maybe.. with a class module with the properties of the class being font type

**snb** · 05-21-2010, 04:17 AM

If you put your 2007 example as an xls file I could have a look.
Preferably also the original Word-file as a doc file.

**pike** · 05-21-2010, 04:21 AM

true it may all be in the import

**wali** · 05-21-2010, 07:01 AM

Hey snb Hey Pike,

i am uploading the word as well as xls file.
What I did was that I replaced all douple line breaks "^p^p" of word document into "§§^pµ". Throug doing that i have now specific symbols for the start and for the end of the string. I then copied and pasted the data into excel. There I removed all number which were indexing the source words after "µ". I then replaced the first "two spaces" into "#" and broke the strings on "#" into two columns. The same I did with transcription. And i have now three columns. Words-->Transcription--> and the remaining string. The result was that in my third post.

What one could also do would be to put all "[?]" in a seperat column as well. [] also contain grammatical identities of the source words and should be seperated.

I will also keep on trying. But I dont think that on my own I will make it to more columns.

**snb** · 05-21-2010, 08:38 AM

My suggestion:

Please Login or Register  to view this content.

**pike** · 05-21-2010, 08:55 AM

my thoughts were along the line of splitting the text into font colours

Please Login or Register  to view this content.

unfinnished

**wali** · 05-23-2010, 01:50 AM

Hey you both,

I can't thank you enough for taking the time for my problem. I love this side of internet and realize each time again and again that Internet without this forum would be almost useless! :-)

I have the data now in three columns and its more than I had imagined I could do.

thanks thanks thanks

Split String into many columns according to Format criteria

LinkBack

Thread Tools

Rate This Thread

Display

Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Re: Split String into many columns according to Format criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions