Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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 :( |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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 :( |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Substitute | Excel Discussion (Misc queries) | |||
More than 7 IF? any substitute? | Excel Worksheet Functions | |||
SUBSTITUTE | Excel Worksheet Functions | |||
Using &Chr$(39)& as substitute for ' in VBA | Excel Discussion (Misc queries) | |||
substitute | Excel Worksheet Functions |