+ Reply to Thread
Results 1 to 24 of 24

search for cells containing only certain letters

  1. #1
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    search for cells containing only certain letters

    Hi ...

    I have treid with Filter, xlookup, search and much much more, but ...

    D1 is where the wanted formula will reside.

    Column A1:A3 contains a list of words.
    C1:C3 contains one letter each.

    How du I get a list of words from column A1:A3 containg only of the letters in C1:C7
    Same letter can be used more than once.

    eks.:
    Column A
    train
    cart
    plane
    aeroplane
    streetcar
    tram
    art

    C1:C3
    a
    r
    t

    Output with spill in D1:
    train
    cart
    plane
    aeroplane
    streetcar
    tram
    art

    I tried =FILTRER(A1:A7,ISNUMBER(SEARCH(C1&"*",A1:A7))) in D1, but it does not give the correct answer.
    It lists all words containg these letters and others, not words containing of only these letters.

    All I want is the last word art.

    VBA?
    Last edited by Tresfjording; 02-16-2022 at 03:23 PM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,713

    Re: search for cells containing only certain letters

    Hi,
    what's in S4 (according to your formula)?

    + do you use excel 365?

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,050

    Re: search for cells containing only certain letters

    Please try

    =FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,A1:A7)&"</m></x>",CONCAT("//m","[contains(.,'"&C1:C3&"')]"))

  4. #4
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    Sorry, that one slipped my eyes. S4 is now changed to A1.

    Yes, I do use Excel 365.

  5. #5
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: search for cells containing only certain letters

    You can use a helper column to first determine if a cell satisfies the condition. For example use this formula in B1 (copied down) -
    Please Login or Register  to view this content.
    This will return TRUE if the word satisfies the condition, else FALSE. Then you can use your Filter function to get the required values.
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  6. #6
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    Hi Bo_Ry

    I get an #CALCULATE error.

  7. #7
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,713

    Re: search for cells containing only certain letters

    Hi,

    try this in C1 and down:
    =IF(ISERROR(SUMPRODUCT(SEARCH({"a","r","t"},A1))),"",A1)
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    Here is an ex file.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    Hi belinda200

    Tried this, but it's not the right outcome. :-)

  10. #10
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: search for cells containing only certain letters

    You should be able to use FILTER function now using the helper column B to filter the required values...

  11. #11
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: search for cells containing only certain letters

    I am using an older version, but this should work in D1 (given that you're using the helper function I provided earlier)-
    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,713

    Re: search for cells containing only certain letters

    Hi Tresfjording
    Are you looking to keep the words that contain both "a","r","t" ?

    if not - please explain what are the results you are expecting to see.....thanks.

  13. #13
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    Hi sourabhg98
    No, too many names in the result
    Only three letter in the rusult is what I want.

  14. #14
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    I'm looking for words that consist of ONLY a, r, and t.

  15. #15
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: search for cells containing only certain letters

    Quote Originally Posted by Tresfjording View Post
    I'm looking for words that consist of ONLY a, r, and t.
    That is what my formula is doing. It should return TRUE for only these words-
    Please Login or Register  to view this content.
    Is any of these words invalid?

  16. #16
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    Hi sourabhg98 ...

    Looking closesr this seems o be working fine.
    Let me test it thouroughly.

  17. #17
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,050

    Re: search for cells containing only certain letters

    Quote Originally Posted by Tresfjording View Post
    I'm looking for words that consist of ONLY a, r, and t.
    Last try

    =LET(z,A1:A7000,x,C1:C3,l,LEN(z),r,ROWS(x),n,ROW(x)^0,m,l-LEN(SUBSTITUTE(LOWER(z),TRANSPOSE(x),)),FILTER(z,(MMULT(--(m>0),n)=r)*(MMULT(m,n)=l)))
    Attached Files Attached Files

  18. #18
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: search for cells containing only certain letters

    Quote Originally Posted by Bo_Ry View Post
    =LET(z,A1:A7000,x,C1:C3,l,LEN(z),r,ROWS(x),n,ROW(x)^0,m,l-LEN(SUBSTITUTE(LOWER(z),TRANSPOSE(x),)),FILTER(z,(MMULT(--(m>0),n)=r)*(MMULT(m,n)=l)))
    That's really cool! (Reminds me that I need to upgrade to Office 365 :p) Although I think OP wanted all words which are entirely made up of those characters but it is not necessary that all of those characters must exist in the word (for example "Ar" should be a valid word). If that's the case, would this be correct?

    Please Login or Register  to view this content.
    Last edited by sourabhg98; 02-17-2022 at 05:28 AM.

  19. #19
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    Hi ...
    That's correct.
    Words only consisting of letters in C:C

  20. #20
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    But I must admit that a list of more than half a million words i A:A makes it too much for my computer, it seems.
    An Intel I5 and 12GB RAM is not enough. Get a not enough memory error message using sourabhg98's solution

  21. #21
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    Yes, but with more than half a milliion words, it all ends in an error message stating not enough memeory. 12GB is not enough.

  22. #22
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    Tweaked a little here today, and sourabhg98's solution seems to work fine. No error messages today.
    Thank you!!!!

  23. #23
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: search for cells containing only certain letters

    You might want to use a VBA solution in that case (if you're facing memory issues) -

    Please Login or Register  to view this content.

  24. #24
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    2016
    Posts
    153

    Re: search for cells containing only certain letters

    Thanks ...

    Worked a a charm. :-D

+ 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] Any two letters search mark true
    By makinmomb in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-13-2017, 02:49 PM
  2. fuzzy search for first four letters
    By jolinchew in forum Excel General
    Replies: 2
    Last Post: 06-10-2015, 05:08 AM
  3. Formula to Find/Search more letters at once?
    By zeko90 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-26-2014, 07:59 AM
  4. [SOLVED] Search CELL-A for one or two letters and display the found letters in CELL-B
    By adamtre in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2014, 02:43 AM
  5. [SOLVED] Search in a compo box using the first 2 letters.
    By MariaPap in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-20-2014, 07:15 AM
  6. Replies: 5
    Last Post: 12-30-2012, 10:59 AM
  7. Replies: 9
    Last Post: 07-15-2010, 08:54 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.10164 seconds
  • Memory Usage 9,563KB
  • Queries Executed 16 (?)
More Information
Template Usage (36):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (5)bbcode_code
  • (3)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (24)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (3)postbit_attachment
  • (24)postbit_legacy
  • (24)postbit_onlinestatus
  • (24)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (50):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1