Results 1 to 4 of 4

Thread: Substitute

  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to use substitute to replace characters such as periods, slashes and dashes in a cell. I only need letters and numbers and spaces are ok too but not required.

    I used the substitute function and stepped my result down to get the solution I needed.
    The problem is I used 3 formulas to do it.

    How can I combine the formulas to remove these characters (they do not have to be replaced with spaces or anything else) into one cell.
    My effort is in the attached workbook.

    ...or am I on the wrong track???
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D3,".",""),"-",""),"/","")

    or write a custom VBA function.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, I could have worked on that for 10 yrs and never come up with that formula.

    Works like a champ. Thanks a bunch.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I simply replaced E3 in the second formula with the first formula (without =), then replaced F3 in the third formula with the second formula (without =)

Posting Permissions

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