=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :(

[lari.saukkonen]

I'm having a problem while using excel 2002.
I have an estimated amount of 3500 cells containing text.

I also have 450 words and for all of them I have abbreviations.

What is the easiest way for me to manipulate those 3500
cells so that if they contained a word defined in this list of
450 words so that those words will be transformed into
each abbreviation?

 

[Bob Phillips]

VBA. Have an Excel table of words and replacements, then loop through all of
the cells, then loop through the list checking for matches.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[lari.saukkonen]

VBA. Have an Excel table of words and replacements, then loop through all of
the cells, then loop through the list checking for matches.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Now that is beyond my skills

 

[Pete_UK]

Here's an old thread that might give you some ideas:

http://groups.google.com/group/micr...069f6b9acac8e0?lnk=st&q=hash+Pete_UK+*excel*#

(Besides, it not often that Harlan admits he screwed up !!)

Hope this helps.

Pete

 

[T. Valko]

Can you post some examples?

 

[lari.saukkonen]

Can you post some examples?

Here's a part from a file that contains triggerwords and those that
should replace them:
KAAPELITILA,KAAPELITIL:KAAPTI
KAAPELI:KAAP
KAAVIO:KAAV
KALANTERI,KALANT:KAL

I have the target file filled with text. For example one line goes:
POISTOPUHALLIN, AUTOM. TILAN KAAPELITILA (2381K4)
so this should be replaced with
POISTOPUHALLIN, AUTOM. TILAN KAAPTI (2381K4)

 

[T. Valko]

Ok, this works but may not be 100% successful. With string matches there's
almost always the chance of false positives. Also note that if there is more
than one keyword in a string this won't work properly.

Create a 2 column table with the keywords in the left column and the
replacement words in the right column. This table *must* be sorted in
ascending order based on the keyword:

KAAPELI ............KAAP
KAAPELITIL.......KAAPTI
KAAPELITILA....KAAPTI
KAAVIO.............KAAV
KALANT............KAL
KALANTERI......KAL

Assume this table is in the range C16

A1 =
POISTOPUHALLIN, AUTOM. TILAN KAAPELITILA (2381K4)

This formula:

=SUBSTITUTE(A1,LOOKUP(2,1/SEARCH(C$1:C$6,A1),C$1:C$6),LOOKUP(2,1/SEARCH(C$1:C$6,A1),D$1$6))

Returns:

POISTOPUHALLIN, AUTOM. TILAN KAAPTI (2381K4)