Replace "~" in Cell of Col. A through the cell before

[wali]

Hello everyone,

i have to replace "~" in column A through the content of cell before which has no "~". I have to process many thousands of dictionary words. For example:


Col. A (before)
long
love
~ly
~one
making ~
be~d
lou
loud
~ly


After running th macro it should give following results:

Col. A (after)
long
love
lovely
loveone
making love
beloved
lou
loud
loudly

Thank you very much for each help in advance.

Please also refer to example excel file!!

[DonkeyOte]

One approach using Evaluate

Please Login or Register  to view this content.

EDIT: ignore the above, I failed to notice consecutive ~ requirement

[DonkeyOte]

Just on an aside - do you definitively need VBA, ie based on your sample

E2:
=IF(ISERR(SEARCH("~~",$A2)),$A2,SUBSTITUTE($A2,"~",LOOKUP(2,1/(ISERR(SEARCH("~~",$A$1:$A1))),$A$1:$A1)))

copied down should give you your results, no ?

[Leith Ross]

Hello Wali,

This macro has been added to the attached workbook. I have run it, and from what I seems to work correctly. But, you need to be the judge.

Please Login or Register  to view this content.

[wali]

Thank you very much to both of you! Million thanks

Dear DonkeyOte

your code works but only for the first "~" but not for the following one. And I cant run the formula cus it shows error. May be because i am using German version of Excel. But thank you very much for your help.


Dear LeitRoss

Your code works perfectly and does what i wanted. You saved me alot of time and nerves. Thank you very very much. God bless you

[shg]

Wali,

If your list starts in A1, DO's formula goes in B2 and is copied down from there. It works perfectly.

May be because i am using German version of Excel.

DO has a link in his sig to function name translations, which should be right up your alley.

[wali]

Dear Shg and Do

thank you very much. Now after translating the function it works perfectly. Thank you very much