Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Any way to do multiple SUBSTITUTE commands in one cell?

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Posts
    157
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Any way to do multiple SUBSTITUTE commands in one cell?

    I have AplphaNumeric part numbers that sometimes contain hyphens or decimals. In order to make them more searchable (since people don't always use the hyphens or decimals properly when they search) I would like to create all the possible variants of the part number, but with one formula.

    So AHW18.787 becomes AHW18 787 and AHW18787 with

    =SUBSTITUTE(A1,"."," ") and
    =SUBSTITUTE(A1,".","") respectively, and

    AHRTW-A18-7007 becomes AHRTW A18 7007 and AHRTWA187007 with

    =SUBSTITUTE(A1,"-"," ") and
    =SUBSTITUTE(A1,"-","").

    What I need to be able to do is merge these formulas into one, so that no matter what format the part number is in A1, I get a version of it with spaces in B1, and a version of it with no spaces in C1. I just have not been able to nest it all together - is it even possible with this command?

    Thx

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,298
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Any way to do multiple SUBSTITUTE commands in one cell?

    Use this

    =SUBSTITUTE(SUBSTITUTE(A1,"-",""),".","")

    This will remove hyphens and full stops leaving you with the other characters.
    This way it doesnt matter whether hyphens or full stops are initally entered.

  3. #3
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Inverclyde, Scotland
    Posts
    6,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Any way to do multiple SUBSTITUTE commands in one cell?

    Try

    =SUBSTITUTE(SUBSTITUTE(A1, "-", ""), ".", "")

  4. #4
    Board Regular
    Join Date
    Nov 2009
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Any way to do multiple SUBSTITUTE commands in one cell?

    Try this for B1:

    =SUBSTITUTE(SUBSTITUTE(A1,"."," "),"-"," ")

    And this for C1:

    =SUBSTITUTE(B1," ","")

  5. #5
    Board Regular
    Join Date
    Mar 2003
    Posts
    157
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Any way to do multiple SUBSTITUTE commands in one cell?

    All Good!!

    Thanks a bunch everyone - I don't know why I have so much trouble w/ that pesky syntax...

    Cheers

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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