+ Reply to Thread
Results 1 to 3 of 3

Problem with multiple SUBSTITUTE in excel

Hybrid View

noly123 Problem with multiple... 03-09-2015, 07:35 PM
TMS Re: Problem with multiple... 03-09-2015, 07:43 PM
Crooza Re: Problem with multiple... 03-09-2015, 08:34 PM
  1. 03-09-2015, 07:35 PM #1
    noly123 is offline
    Registered User
    Join Date
    03-09-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    1

    Problem with multiple SUBSTITUTE in excel

    Hi folks,
    I used the following script in excel, but the results did not turn out right. I am not sure what is wrong here. Some errors are highlighted. Thank you

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2:$A$40,"BA_ICPMS","Barium"),"AL_ICPMS","Aluminium"),"AS_ICPMS","Arsenic"),"CD_ICPMS","Cadmium"),"CL_IC","Chloride"),"CO3","Carbonate"),"COND","Conductivity"),"B","Boron"),"CA","Calcium"),"CR_ICPMS","Chromium"),"CU_ICPMS","Copper"),"F_IC","Flouride"),"FE","Iron"),"HCO3","Bicarbonate"),"HDNESS","Hardness"),"K","Potassium"),"MG","Magnesium"),"MN","Manganese"),"NA","Sodium"),"NO3_IC","Nitrate"),"OH","Hydroxide"),"PALK","Phenol Alkalinity"),"PB_ICPMS","Lead"),"PH","pH"),"SE_ICPMS","Selenium"),"SO4_IC","Sulfate"),"TALK","Total Alkalinity"),"TDS","Total Dissolved Solids"),"U_ICPMS","Uranium"),"ZN_ICPMS","Zinc"),"OPO4","Ortho Phosphorus"),"MO_ICPMS","Molybdenum")

    Results (in two separate columns
    AL_ICPMS Aluminium
    AS_ICPMS Arsenic
    B Boron
    BA_ICPMS Boronarium
    CA Calcium
    CD_ICPMS Cadmium
    CL_IC Chloride
    CO3 Carbonate
    COND Conductivity
    CR_ICPMS Chromium
    CU_ICPMS Copper
    F_IC Flouride
    FE Iron
    HCO3 HCarbonate
    HDNESS Hardness
    K Potassium
    MG Magnesium
    MN Manganese
    NA Sodium
    NO3_IC Nitrate
    OH Hydroxide
    PALK PALPotassium
    PB_ICPMS PBoron_ICPMS
    PH pH
    SE_ICPMS Selenium
    SO4_IC Sulfate
    TALK TALPotassium
    TDS Total Dissolved Solids
    U_ICPMS Uranium
    ZN_ICPMS Zinc
    CA Calcium
    CL_IC Chloride
    CO3 Carbonate
    COND Conductivity
    F_IC Flouride
    FE Iron
    HCO3 HCarbonate
    HDNESS Hardness
    K Potassium
    MG Magnesium
    MN Manganese
    NA Sodium
    NO3_IC Nitrate
    OH Hydroxide
    OPO4 Ortho Phosphorus
    PALK PALPotassium
    PH pH
    SO4_IC Sulfate
    TALK TALPotassium
    Reply With QuoteRegister To Reply
  2. 03-09-2015, 07:43 PM #2
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,201

    Re: Problem with multiple SUBSTITUTE in excel

    It's finding secondary charactersets and changing them, sometimes before the thing you want to change. For example, it is finding the K in PALK and substituting Potassium. It then won't find PALK to convert it.

    The usual way to do this scale of conversion is with a lookup table and VLOOKUP.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 03-09-2015, 08:34 PM #3
    Crooza is offline
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Problem with multiple SUBSTITUTE in excel

    If you chnage the order to put Potasium at the end of the list hten you won't have this problem

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$3:$B$51,"BA_ICPMS","Barium"),"AL_ICPMS","Aluminium"),"AS_ICPMS","Arsenic"),"CD_ICPMS","Cadmium"),"CL_IC","Chloride"),"CO3","Carbonate"),"COND","Conductivity"),"B","Boron"),"CA","Calcium"),"CR_ICPMS","Chromium"),"CU_ICPMS","Copper"),"F_IC","Flouride"),"FE","Iron"),"HCO3","Bicarbonate"),"HDNESS","Hardness"),"MG","Magnesium"),"MN","Manganese"),"NA","Sodium"),"NO3_IC","Nitrate"),"OH","Hydroxide"),"PALK","Phenol Alkalinity"),"PB_ICPMS","Lead"),"PH","pH"),"SE_ICPMS","Selenium"),"SO4_IC","Sulfate"),"TALK","Total Alkalinity"),"TDS","Total Dissolved Solids"),"U_ICPMS","Uranium"),"ZN_ICPMS","Zinc"),"OPO4","Ortho Phosphorus"),"MO_ICPMS","Molybdenum"),"K","Potassium")
    Happy with my advice? Click on the * reputation button below
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Is there another way to write INDEX(INDIRECT(SUBSTITUTE()),MATCH(OFFSET(SUBSTITUTE()))?
    By keby1nko in forum Excel General
    Replies: 4
    Last Post: 10-28-2014, 11:04 PM
  2. [SOLVED] Concatenate & substitute problem
    By norm01 in forum Excel General
    Replies: 2
    Last Post: 03-08-2013, 11:31 AM
  3. SUBSTITUTE problem
    By MrRed in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-09-2012, 12:18 PM
  4. =SUBSTITUTE Problem
    By seanjacob in forum Excel General
    Replies: 3
    Last Post: 09-23-2010, 06:04 AM
  5. Indirect substitute problem
    By RooTFantastic in forum Excel General
    Replies: 1
    Last Post: 02-17-2009, 06:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1