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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lari.saukkonen@gmail.com - 21 May 2008 07:00 GMT

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?

Reply to this Message

Bob Phillips - 21 May 2008 09:37 GMT

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

Signature

---
HTH

Bob

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

> I'm having a problem while using excel 2002.
> I have an estimated amount of 3500 cells containing text.
[quoted text clipped - 5 lines]
> 450 words so that those words will be transformed into
> each abbreviation?

Reply to this Message

lari.saukkonen@gmail.com - 21 May 2008 09:47 GMT

> VBA. Have an Excel table of words and replacements, then loop through all of
> the cells, then loop through the list checking for matches.
[quoted text clipped - 6 lines]
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)

Now that is beyond my skills :(

Reply to this Message

Pete_UK - 21 May 2008 10:03 GMT

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

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse
_frm/thread/b58329abe022da81/0f069f6b9acac8e0?lnk=st&q=hash+Pete_UK+*excel*#

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

Hope this helps.

Pete

On May 21, 9:47 am, lari.saukko...@gmail.com 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.
[quoted text clipped - 8 lines]
>
> Now that is beyond my skills :(

Reply to this Message

T. Valko - 21 May 2008 20:25 GMT

Can you post some examples?

Signature

Biff
Microsoft Excel MVP

Reply to this Message

lari.saukkonen@gmail.com - 22 May 2008 12:28 GMT

> 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)

Reply to this Message

T. Valko - 22 May 2008 17:33 GMT

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)

Signature

Biff
Microsoft Excel MVP

>> Can you post some examples?
>>
[quoted text clipped - 13 lines]
> so this should be replaced with
> POISTOPUHALLIN, AUTOM. TILAN KAAPTI (2381K4)

Reply to this Message