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

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?

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)



wrote in message
...
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?

On 21 touko, 11:37, "Bob Phillips" wrote:
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

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

http://groups.google.com/group/micro...te_UK+*excel*#

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

Hope this helps.

Pete

On May 21, 9:47*am, wrote:
On 21 touko, 11:37, "Bob Phillips" wrote:

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

Can you post some examples?

--
Biff
Microsoft Excel MVP


wrote in message
...
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?

On 21 touko, 22:25, "T. Valko" wrote:
Can you post some examples?

--
Biff
Microsoft Excel MVP

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)

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)


--
Biff
Microsoft Excel MVP


wrote in message
...
On 21 touko, 22:25, "T. Valko" wrote:
Can you post some examples?

--
Biff
Microsoft Excel MVP

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)