+ Reply to Thread
Results 1 to 7 of 7

Limited character search into exisiting formula. PLEASE HELP!

  1. #1
    Registered User
    Join Date
    08-23-2018
    Location
    Phoenix, USA
    MS-Off Ver
    2010
    Posts
    2

    Limited character search into exisiting formula. PLEASE HELP!

    Hi All.

    I have a formula that is looking for matching values in column A & B and aligning the matches for a in C:

    I.E

    A B C
    1 2 1
    5 - 5
    2 1 2
    4 5

    The formula is: =IF(ISNA(MATCH(A8,B:B,0)),"",INDEX(B:B,MATCH(A8,B:B,0))) and works perfect.

    Is there a way that I can use this formula to only search and match the first 5 characters of column A & B.
    Ive tried using the left,6 but cant figure out where to insert it.

    I.E
    Column A / Column B / Column C
    12345-A --- 12344-C --- 12345
    12349-F --- 12342-A
    12323-F --- 12345-B --- 12323
    12992-A --- 12323-F

    If I cant use my existing formula for this can anyone tell me one that will do the same function?
    Along with this is it possible to run a conditional format to highlight the blank cells in column C?

    Thanks!
    Last edited by RFreddy; 08-23-2018 at 02:30 AM.

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Limited character search into exisiting formula. PLEASE HELP!

    Attach a sample workbook, showing what you want as a result for that sample.

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Limited character search into exisiting formula. PLEASE HELP!

    And please check your expected answers, as your explanation is not at all clear to me!!
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: Limited character search into exisiting formula. PLEASE HELP!

    Maybe………….

    1] Assume your data in A1:B4

    2] In C1, copied down :

    =IF(ISNUMBER(LOOKUP(9^9,SEARCH(LEFT(A1,5),LEFT($B$1:$B$4,5)))),LEFT(A1,5),"")

    Regards
    Bosco

  5. #5
    Registered User
    Join Date
    08-23-2018
    Location
    Phoenix, USA
    MS-Off Ver
    2010
    Posts
    2

    Re: Limited character search into exisiting formula. PLEASE HELP!

    Sorry for the ambiguity.

    This is my first time using the forum.
    Ive tried to attach a simple workbook for example.

    So I have a heap of data in column A, approximately 2000 lines, and another 1500 lines of data in column B.
    I want the formula search for matches (not aligned) and give a result in column C.
    This is currently working with the original formula however I need it to match partial results as well, first 5 characters of my data.

    in the attachment you can see the "current formula" example matches words in A & B and puts the word into C.
    the second example shows no results in C because there is no perfect matches.
    the third example shows what i need, the formula has matched partial results (for ease of example i have used simple names but I need it to look for matches in the first 5 characters of each cell)

    if still not clear I can elaborate more.

    Thanks.
    Attached Files Attached Files

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

    Re: Limited character search into exisiting formula. PLEASE HELP!

    Hello RFreddy and Welcome to Excel Forum.
    This proposed solution employs two helper columns which may be moved and/or hidden for aesthetic purposes.
    The helper columns (I:J in the attached file) are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Column H is populated using your original formula with references to the helper columns.
    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.

  7. #7
    Forum Contributor
    Join Date
    08-14-2018
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    118

    Re: Limited character search into exisiting formula. PLEASE HELP!

    Hi RFreddy
    Try this ARRAY formula (C2, H2, M2 then copied down)
    Please Login or Register  to view this content.
    Finish by pressing CTRL + SHIFT + ENTER
    HTH.

+ 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. Replies: 3
    Last Post: 01-20-2016, 10:48 AM
  2. Pivot search limited to 10,000, how do I exclude fields then please?
    By mike_vr in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-24-2014, 07:56 AM
  3. Pivot search limited to 10,000, how do I exclude then?
    By mike_vr in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-24-2014, 05:51 AM
  4. Search and Replace for a limited rows
    By harshj in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-23-2013, 01:11 AM
  5. Replies: 1
    Last Post: 05-14-2006, 12:00 AM
  6. Macro Print to file limited the no of character in a row?
    By Leslie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-30-2005, 12:00 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