+ Reply to Thread
Results 1 to 21 of 21

Excel formula for pulling specific names from fields Part 2

  1. #1
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Excel formula for pulling specific names from fields Part 2

    Hello, I previously posted (August 24,2022) an issue I was having with needing an Excel formula that would return specific names (in my case vendors) from specific fields. [see "Thread: Excel formula for pulling specific names from fields" by ace8pga8. The formula i ended up using is the following: =IFERROR(LOOKUP(1000,SEARCH($F$4:$F$18,A4),$G$4:$G$18),""). This formula proved very useful with approximately 95% success rate, with the exception that when 2 or more vendors in my lookup vector would pull in the incorrect vendor name. For example if the description field contained 'Microsoft' & 'Teradata', it would return 'Teradata', even though it should be Microsoft. I am attaching two examples in an Excel template that show this occurring. If you will require further data please dont hesitate to ask...
    Attached Files Attached Files

  2. #2
    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: Excel formula for pulling specific names from fields Part 2

    You have provided a VERY limited sample.

    Try:

    =TEXTAFTER(TEXTBEFORE(B5," "),"-")
    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

  3. #3
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Re: Excel formula for pulling specific names from fields Part 2

    will do Glenn ty...keep you posted.

  4. #4
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Re: Excel formula for pulling specific names from fields Part 2

    Glenn, where would the TEXTAFTER/BEFORE formula be embedded within the LOOKUP/SEARCH formula?

  5. #5
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Re: Excel formula for pulling specific names from fields Part 2

    Good morning Glenn...unfortunately the TEXTBEFORE/AFTER formula can only be used when using Beta Office on the Office Insider Program. I don't have that capability. Is there another formula you can recommend? Thanks!

  6. #6
    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: Excel formula for pulling specific names from fields Part 2

    Please modify your profile to show the current O365 VERSION you are using (model it on mine). Those functions are on general release. However, your version may not be sufficiently up-to-date.
    Attached Images Attached Images

  7. #7
    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: Excel formula for pulling specific names from fields Part 2

    This will work on any PC version of Excel, post 2013:

    =FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(B5," -","-"),"-","</B><B>")&"</B></A>","//B[2]")
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Re: Excel formula for pulling specific names from fields Part 2

    Here is my version Glenn...
    Capture.PNG

  9. #9
    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: Excel formula for pulling specific names from fields Part 2

    Yes, the semi-annual EC is still on 2202... the current release channel is 2210.

    However, the formula at Post 7 should work for you. Did it?

  10. #10
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Re: Excel formula for pulling specific names from fields Part 2

    Not sure how to use the formula...do I embed within the LOOKUP/SEARCH formula i am currently using or as a stand alone?

  11. #11
    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: Excel formula for pulling specific names from fields Part 2

    Ah yes... I'm a fool. See amended formula and sheet.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Re: Excel formula for pulling specific names from fields Part 2

    Ok...this might work....need to test it out on my dataset. Ill keep you posted to see if it works...Thanks brother!

  13. #13
    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: Excel formula for pulling specific names from fields Part 2

    Keep me posted...

  14. #14
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Re: Excel formula for pulling specific names from fields Part 2

    Hi Glenn, I instituted the formula with limited success. I am attaching a larger sample size with which to analyze in the attached file.
    Please let me know if you need anything else.
    Attached Files Attached Files

  15. #15
    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: Excel formula for pulling specific names from fields Part 2

    The formula returns the string AFTER the first - and before the first space. It looks up the value in column D and returns the value in column B.

    It doesn't find things that aren't there, though...
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Re: Excel formula for pulling specific names from fields Part 2

    ok...so it looks like you embedded the FILTERXML formula within the LOOKUP/SEARCH function. By the way i now have access to the TEXTBEFORE/TEXTAFTER function in Excel. not sure if it makes a difference at this point....Thanks for your help!

  17. #17
    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: Excel formula for pulling specific names from fields Part 2

    No... it'll not make any material difference. The major issue is/was the lack of a clean delimiter that could be used in all cases.

    Anyhow....

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

  18. #18
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Re: Excel formula for pulling specific names from fields Part 2

    Thanks Glenn...not solved yet....another question...what do we do in cases where there is no delimiter? i thought the formula would search for those without delimiters after searching for those with delimiters. any ideas? see Verizon example in attached.
    Attached Files Attached Files

  19. #19
    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: Excel formula for pulling specific names from fields Part 2

    There is a drip, drip, drip of further exceptions. I think I'll step back at this point. Over to others.

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Excel formula for pulling specific names from fields Part 2

    Please Login or Register  to view this content.
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  21. #21
    Registered User
    Join Date
    08-10-2022
    Location
    california, usa
    MS-Off Ver
    365
    Posts
    23

    Re: Excel formula for pulling specific names from fields Part 2

    Thanks for your time Glenn! Much appreciated...

+ 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] Excel formula for pulling specific names from fields
    By ace8pga8 in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 08-17-2022, 05:24 PM
  2. [SOLVED] Extraction formula pulling 1 too many names not matching the constant value
    By mrteater in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-22-2022, 09:21 AM
  3. Array summing inventory of a specific part (array formula) EXCEL 2007
    By fdirosa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-07-2018, 12:51 PM
  4. [SOLVED] Click/drag a formula pulling from differ sheets w/ excel auto changing sheet names only
    By GonzCilla in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2018, 02:16 PM
  5. [SOLVED] IF Formula. Time between 2 dates taking into account Blank fields PART 2
    By Matt1998 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-25-2015, 11:31 AM
  6. VBA? Formula for pulling multiple dates for one part. Please help
    By bernice620 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-13-2012, 11:23 AM
  7. Excel Pulling Names from Global Address List
    By mauddib13 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-09-2008, 12:49 AM

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