+ Reply to Thread
Results 1 to 4 of 4

Creating formula that extracts strings in a cell that match strings in another

  1. #1
    Registered User
    Join Date
    11-07-2019
    Location
    Idaho, USA
    MS-Off Ver
    2016
    Posts
    2

    Creating formula that extracts strings in a cell that match strings in another

    I have unsuccessfully searched the web over for possible solutions to this problem, let me see if I can describe it...

    I have a data set with a column medical codes that have extra characters and things on the beginning or/and or end of the string and a separate data set with a column that contains valid codes. I want to be able use a formula to fix the codes with extra characters so that they return the valid code that is a part of the messy code. Basically, I'm thinking I could accomplish my goal if I could create a formula that said something like this "if a cell contains consecutive characters that match anyone of the strings in this column return the match from the column". Maybe something with INDEX or MATCH???

    For example I have code FARJ34561, the valid code that I want it to be is J3456.

    Hope that makes sense,
    Thanks!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Creating formula that extracts strings in a cell that match strings in another

    It doesn't make much sense with only one isolated example - it would be better to attach a sample Excel workbook (NOT a picture or image of one).

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-07-2019
    Location
    Idaho, USA
    MS-Off Ver
    2016
    Posts
    2

    Re: Creating formula that extracts strings in a cell that match strings in another

    Okay here is a sample file, the "Messy" column is the column I want to clean up and match with the values in the valid column, hope this helps.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Creating formula that extracts strings in a cell that match strings in another

    Perhaps this will be of some help.
    Column B is populated using: =COUNTIFS(C$2:C$20,"*"&A2&"*")>0
    This will identify possible matches.
    Column E is populated using: =INDEX(A$2:A$16957,AGGREGATE(15,6,(ROW(A$2:A$16957)-ROW(A$1))/(B$2:B$16957=TRUE),ROWS(A$1:A1)))
    This makes a list of the 25 possible matches.
    There are still issues however. The two values highlighted in green appear to be one-to-one matches.
    Values in column C highlighted in other colors could be matched to more than one value in column E.
    There also appear to be some values in column C that do not have matches.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Find Multiple Text Strings in Column, return the 9 cell strings below
    By BoExcels in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2019, 09:56 AM
  2. Macro to Delete only Alpha Strings but not Alphanumeric or Numeric Strings in a Cell
    By papageorgio in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2016, 06:55 AM
  3. Match different long strings with an array of short, summarizing strings
    By FKemps in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-24-2015, 12:55 AM
  4. excel formula to search Multiple strings in several columns and return strings
    By krratna123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-13-2013, 11:20 AM
  5. Replies: 1
    Last Post: 08-13-2013, 08:32 AM
  6. [SOLVED] loop to match if shorter strings appears in longer strings
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2012, 08:07 PM
  7. Replies: 3
    Last Post: 05-28-2011, 01:43 PM

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