+ Reply to Thread
Results 1 to 12 of 12

Find & Replace all characters in cell like ";#"

  1. #1
    Registered User
    Join Date
    10-14-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Find & Replace all characters in cell like ";#"

    Hi:
    I need help with a formula (no VBA) that will find all the semi-colons ";" and pound signs "#" in a cell and replace them with this "<br> - ".

    Example of Cell A1: ;#Red;#Green;#Blue;#

    I started with this:

    =REPLACE(MultChoice,SEARCH(";#",MultChoice),1,"<br> - ")

    but it only replaces the first character.

    Can this be done?

    Thanks- Charlie Epes
    Buffalo, NY
    Last edited by chasepes; 10-14-2009 at 11:45 AM. Reason: Shorten Title

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Find & Replace all characters in cell like ";#"

    Welcome to the forum.

    =SUBSTITUTE(A1, ";#", "<br> - ")
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-14-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Find & Replace all characters in cell like ";#"

    Hi SHG:
    Thanks for the quick response.

    I'm actually working in SharePoint which uses a very shortened selection of Excel functions. I should have noted this up-front... Not many SharePoint folks are savvy at function.

    Can this be done any other way? My available Text-related functions are:
    ASC function

    CHAR function

    CLEAN function

    CODE function

    CONCATENATE function

    DOLLAR function

    EXACT function

    FIND function

    FIXED function

    LEFT function

    LEN function

    LOWER function

    MID function

    PROPER function

    REPLACE function

    REPT function

    RIGHT function

    SEARCH function

    T function

    TEXT function

    TRIM function

    UPPER function

    USDOLLAR function

    VALUE function

    Charlie Epes
    Buffalo, NY

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find & Replace all characters in cell like ";#"

    Can you also not do a physical Find/Replace as in CTRL+H and replace each character with your string...separately?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    10-14-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Find & Replace all characters in cell like ";#"

    Hi NBVC:
    No, I need an automatic formula. This is an ongoing task.

    Thanks-

    Charlie Epes

  6. #6
    Registered User
    Join Date
    10-14-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Find & Replace all characters in cell like ";#"

    I may have to resort to a formula like the following but I'm not sure how to amend it to accomodate almost 25 possibilities.

    =REPLACE(REPLACE(REPLACE(A1,SEARCH(";#",A1),1,""),SEARCH(";#",REPLACE(A1,SEARCH(";#",A1),1,"")),1,""),SEARCH(";#",REPLACE(REPLACE(A1,SEARCH(";#",A1),1,""),SEARCH(";#",REPLACE(A1,SEARCH(";#",A1),1,"")),1,"")),1,"")

    Charlie Epes

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find & Replace all characters in cell like ";#"

    If you use this formula:
    =REPLACE(A1,SEARCH(";#",A1),2,"<br> - ")

    where A1 contains original full string.

    and copy it across the columns, it progressively does the replacements... then you can use the last cells' results?

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find & Replace all characters in cell like ";#"

    It also looks like ISNUMBER() is an allowable Sharepoint function...

    so...

    to avoid #VALUE errors..

    =IF(ISNUMBER(SEARCH(";#",A1)),REPLACE(A1,SEARCH(";#",A1),2,"<br> - "),"")

    copied across...

  9. #9
    Registered User
    Join Date
    10-14-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Find & Replace all characters in cell like ";#"

    Hi:
    There's no copying across since I'm working in SharePoint and I have one field that acts on another field.

    The result I get is: <br> - Red;#Green;#Blue;#

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find & Replace all characters in cell like ";#"

    I've never worked with SharePoint.. so don't know the intricacies...

    If there is no limit on nested functions, then that might be your only alternative..

  11. #11
    Registered User
    Join Date
    10-14-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Find & Replace all characters in cell like ";#"

    OK thank you very much for your time-

    BTW, SharePoint does many things and the calculated field uses Excel, but whittled down.

    Charlie Epes

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find & Replace all characters in cell like ";#"

    Have you tried posting at a Sharepoint forum? Maybe they have some tricks up their sleeves...

    Here's a couple:

    http://www.tek-tips.com/threadminder.cfm?pid=820

    http://www.sharepointforum.com/en-US/Pages/default.aspx

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1