+ Reply to Thread
Results 1 to 8 of 8

how to create UDF for multiple substitutions; find & replace values are in another sheet

  1. #1
    Registered User
    Join Date
    03-02-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    how to create UDF for multiple substitutions; find & replace values are in another sheet

    I'm trying to find a UDF formula that will replace data in one column in Sheet 1 (called "GL_Detail) with values from Sheet 2 (called "Replace_Values).

    Column A in "GL_Detail" has the following invoice numbers:
    10000
    10001-in
    10002-CM
    10003-3
    10004-a
    10005-A
    JE# 5000

    I want users to write in the "Replace_Values" sheet in Column A, the value to be replaced (for instance, the "-in", "-CM", "-3", etc) and in Column B the replacing value (in this case, all blanks).

    Column B in "GL_Detail" will then show the following:
    10000
    10001
    10002
    10003
    10004
    10005
    JE# 5000

    Thanks for your help.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: how to create UDF for multiple substitutions; find & replace values are in another she

    Is this what you wanted?
    Please Login or Register  to view this content.
    Missed
    in Column B the replacing value
    B1
    =LEFT(A1,FIND("-",A1&"-")-1)
    Then filldown

    vba
    Please Login or Register  to view this content.
    Last edited by jindon; 03-05-2015 at 01:54 AM.

  3. #3
    Registered User
    Join Date
    03-02-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    Re: how to create UDF for multiple substitutions; find & replace values are in another she

    Thanks, but some of my invoices don't have the "-." In addition, invoice numbers are of different length. How would this affect the code?

    For instance, i have another set of invoices that are mixed in with the numbers from above: 1234B3, 1235B3, 1236b003. The invoice should read: 1234, 1235, and 1236.
    Another set reads: TX10000B14, TX10001B14, and TX1000002B14. The invoice should read: TX10000, TX10001, and TX10002.

    Lastly, does the code above need to be saved to a specific location in VBA? If so, then can you please point me where? Thanks so much for your help.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: how to create UDF for multiple substitutions; find & replace values are in another she

    Still not clear to me.

    These 2 UDFs generate almost same result but the concepts are different.
    1) GetInv function will pull string that is Numbers with/without non numeric characters.
    =GetInv(A1)
    Please Login or Register  to view this content.
    2) RemoveAtEnd fucntion removes any characters after last numeric character
    =RemoveAtEnd(A1)
    Please Login or Register  to view this content.
    The difference is demonstrated in the attached.
    To see VBA code, hit Alt + F11
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-02-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    Re: how to create UDF for multiple substitutions; find & replace values are in another she

    Thank you for the excel example. The only item missing is that the invoice column also contains journal entry numbers followed by journal entry descriptions (all within the same cell). I've attached an example of the GL download that I get (column A) and have to filter manually each month to get the results in Column B.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: how to create UDF for multiple substitutions; find & replace values are in another she

    Here's how I see the logic and I don't think this is done by simple Replace,Substitute function....

    See attached.
    If you have more irregular data, I need to see them.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-02-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    Re: how to create UDF for multiple substitutions; find & replace values are in another she

    Actually this is good. This will work very nicely. Thank you for time.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: how to create UDF for multiple substitutions; find & replace values are in another she

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    If you find any problem with this, just post back with the data and the result that you want.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA Find & Replace Multiple Values
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2013, 03:54 AM
  2. [SOLVED] Faster way to find and replace multiple values with corresponding values
    By babbeleme1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-18-2013, 06:50 PM
  3. Find / Replace with Multiple Values
    By ciprian in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-29-2011, 02:30 AM
  4. Find and Replace values from multiple lookup values
    By Gregula82 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2007, 03:12 PM
  5. find and replace multiple values
    By nikki115 in forum Excel General
    Replies: 1
    Last Post: 09-13-2006, 01:41 PM

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