Cut/Paste Parts of Strings from one Column to Other Columns

**mlexcelhelpforum** · 07-08-2011, 04:19 AM

Hello,

I have a dictionary file in excel. Only the first column is filled with data entries, which look exactly like this, but with many more entries:

1st row: 一局一局 [yi1 ju2] /inning/
2nd row: 一層一层 [yi1 ceng2] /layer/
3rd row: 一巴掌一巴掌 [yi1 ba1 zhang3] /a slap/a spank/

The desired end result is:

1st Column
1st row: 一局一局
2d row: 一層一层
3rd row: 一巴掌一巴掌

2nd Column
1st row: yi1 ju2
2d row: yi1 ceng2
3rd row: yi1 ba1 zhang3

3rd Column
1st row: inning
2d row: layer
3rd row: a slap, a spank

The problem is I don't know how to specify and manipulate elements of the strings.

Thank you kindly for any assistance!

ML

**Domski** · 07-08-2011, 04:41 AM

Try using Text To Columns. Use [ as the delimiter first time and ] the second time. The rest you should be able to clean up using Find and Replace.

Dom

**TMS** · 07-08-2011, 05:02 AM

Try this for a starter:

Please Login or Register  to view this content.

If it looks OK, you can un-comment the line: "Columns(1).Delete"

Regards

**mlexcelhelpforum** · 07-08-2011, 05:28 AM

Dear Dom,

Thanks for the tip! I checked out the Text to Columns feature. Interesting. I didn't know about it. I'll fiddle with it more later.

Dear TMShucks,

I tried it out, and it worked perfectly! Thank you oh so much!

However, on second thought I realized it would be better to also split the two Chinese words (which are separated by a space in the original column) into their own individual column, so that the end result looks like this:

1st Column
1st row: 一局
2d row: 一層
3rd row: 一巴掌

2nd Column
1st row: 一局
2d row: 一层
3rd row: 一巴掌

3nd Column
1st row: yi1 ju2
2d row: yi1 ceng2
3rd row: yi1 ba1 zhang3

4th Column
1st row: inning
2d row: layer
3rd row: a slap, a spank

Would it be possible to add in a small section of code somewhere to include that step?

Thank you soooo much!

**TMS** · 07-08-2011, 06:11 AM

Seeing as you asked so nicely ...

Please Login or Register  to view this content.

but no more ;-)

Regards

**mlexcelhelpforum** · 07-08-2011, 06:50 AM

Dear TMShucks,

I gave it a shot, and something didn't go quite right. So I went and studied the code, figured out the gist of what it meant and realized that just a small piece needed to be changed into this:

Please Login or Register  to view this content.

It was probably just a typo on your end, but nevertheless it forced me to have a look at the code now rather than save it for another day and left me feeling slightly capable!

Thanks a lot for the solution!

ML

**TMS** · 07-08-2011, 07:40 AM

You are absolutely right, well spotted ... sadly, it *was* my error.

Not a typo, as such. I often record a macro to get the base code and then generalise it. That's what I did here just to make sure what I wanted to do worked correctly. Of course, I was only working with three rows so I didn't notice a problem. What I forgot to do was apply the edit which you have quite correctly put in.

Anyway, it's maybe helped you down the learning curve so every cloud has a silver lining.

Thanks for the rep.

Regards