+ Reply to Thread
Results 1 to 3 of 3

Replace last comma in string of text

  1. #1
    Registered User
    Join Date
    09-20-2012
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    29

    Replace last comma in string of text

    Hello,
    I'd like to write a formula that will replace the last comma in a string of text with the word "and" but only if there are two or more commas in the referenced cell.

    For example, a cell with the text "technology, health care, communications" would become "technology, health care and communucations". A cell with "technology and healthcare" would stay "technology and healthcare".

    I have the following formula currently to replace the last comma, but I don't know how to add an argument that will tell the formula to replace the comma only if there are more than one: =IFERROR(SUBSTITUTE($L15,",",", and",LEN($L15)-LEN(SUBSTITUTE($L15,",",""))),$L15)

    Any suggestions?

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Replace last comma in string of text

    The way to test for two or more commas in L15 is:

    =IF(LEN(L15)-LEN(SUBSTITUTE(L15,",",""))>=2,"two or more commas","less than two commas")

    You need to combine this with your other formula
    Gary's Student

  3. #3
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Replace last comma in string of text

    there is a slight error in your formula i think (it wasn't changing "," to "and" it was changing "," to ", and" which it looks like you don't want also in case you get extra spaces you might want to trim it so your final formula would be this

    Please Login or Register  to view this content.
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1