Cut words from a cell and paste them into columns

[Mosamba]

Hi guys,
Sorry but I have know knowledge at all about VB
In order to create a bi-lingual glossary, I'm in need of a function which must:


1. cut from column 1 and paste in column 2, the word(s) contained between the sign "=" exclusively and the sign "(" also exclusively;
2. cut also from column 1 and paste in column 3, the word(s) contained between the sign "(" inclusively, and the sign ")" also inclusively.
This operation should be repeated for approximately 1500 lines.
Should it simplify, you may generate one macro which does step 1 and another which does step 2, separately.


Appreciate very much your help.

 

[0verride]

Hello,
Is all the text always in the same format?
For example.. is every text like BLABLABLA=TEST(WORD)

 

[Mosamba]

Hello,
Is all the text always in the same format?
For example.. is every text like BLABLABLA=TEST(WORD)

Yes it is. Following is an example of all the lines in column 1:

residual stress = tensão residual (Tensão existente mesmo após
cessarem os esforços aplicados no maciço rochoso ou outro
material sólido)

The words "residual stress" remain in column 1;
the words "tensao residual" are to be cut and pasted in column 2, and;
the ones in between the brackets are to be cut and pasted in column 3.

Thanks indeed for your interest!
P.S.: I have Excel 2007

 

[0verride]

Oke, here's what I would do:
You don't actually need a VBA code for this.

Assuming you have all these values in column A.
Paste this in Column B: =LEFT(A1;SEARCH("=";A1))
Paste this in Column C: =RIGHT(LEFT(A1;SEARCH("(";A1)-1);SEARCH("(";A1)-SEARCH("=";A1)-1)
Paste this in Column D: =RIGHT(LEFT(A1;SEARCH(")";A1)-1);SEARCH(")";A1)-SEARCH("(";A1)-1)

Then just drag the whole thing down to where you want it and delete column A. Presto... you are finished

 

[Mosamba]

Oke, here's what I would do:
You don't actually need a VBA code for this.

Assuming you have all these values in column A.
Paste this in Column B: =LEFT(A1;SEARCH("=";A1))
Paste this in Column C: =RIGHT(LEFT(A1;SEARCH("(";A1)-1);SEARCH("(";A1)-SEARCH("=";A1)-1)
Paste this in Column D: =RIGHT(LEFT(A1;SEARCH(")";A1)-1);SEARCH(")";A1)-SEARCH("(";A1)-1)

Then just drag the whole thing down to where you want it and delete column A. Presto... you are finished

Great Override.
Thanks indeed for the hint. I'll be testing it and send you feedback ASAP.

 

[Mosamba]

Override,
I have followed your instructions and in each column I've pasted your script there is an error saying "#NAME?" (the formula contains non-recognized text).
Sorry I don't know what it means.

 

[0verride]

Override,
I have followed your instructions and in each column I've pasted your script there is an error saying "#NAME?" (the formula contains non-recognized text).
Sorry I don't know what it means.

And what happens if you try these?
B: =IZQUIERDA(A1;HALLAR("=";A1)-1)
C: =DERECHA(IZQUIERDA(A1;HALLAR("(";A1)-1);HALLAR("(";A1)-HALLAR("=";A1)-1)
D: =DERECHA(IZQUIERDA(A1;HALLAR(")";A1)-1);HALLAR(")";A1)-HALLAR("(";A1)-1)

#NAME means a language problem... you are probably using a spanish version of excel right?

Hope this helps

 

[Mosamba]

Override,
I'm not seeing my post just after your messsage, so I'm repeating it:
Very wise if you. It was actually the language problem.

But there is still a glitch which I tried to work around without success:

Columns B and D are PERFECT!
Column C though is showing the word from B + the word that actually should be in this column.
Is it possible to REMOVE from this column the word that is already in B and leaving ONLY the other one (which is the translated term)?
Appreciate this!

 

[0verride]

Override,
I'm not seeing my post just after your messsage, so I'm repeating it:
Very wise if you. It was actually the language problem.

But there is still a glitch which I tried to work around without success:

Columns B and D are PERFECT!
Column C though is showing the word from B + the word that actually should be in this column.
Is it possible to REMOVE from this column the word that is already in B and leaving ONLY the other one (which is the translated term)?
Appreciate this!

Mmm that is strange, that should be exactly what the code is doing.
Can you post an example with your TEXT and the response it generates in column C?

 

[Peter_SSs]

You'll have to do the language conversion, but you could try these.

Excel Workbook
	A	B	C	D
1	residual stress = tenso residual (Tenso existente mesmo aps cessarem os esforos aplicados no macio rochoso ou outro material slido)	residual stress =	tenso residual	Tenso existente mesmo aps cessarem os esforos aplicados no macio rochoso ou outro material slido
Extract Text