=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?

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

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?

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

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 :(

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

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 :(

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

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?

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

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)

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

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 C1:D6

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:D$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)