Looking for formula to search for particular pattern in Excel











up vote
-2
down vote

favorite












I am looking for formula to search for "words" with a particular letter pattern, like INNSA1 or INMAA4 or INPTPB, where the word starts with IN and ends with 4, 1, 6, or B.



A collection of Excel cells each contain a sentence in which one such word may be found. For example:




  • My Custom location is INNSA1

  • I work at INMAA4

  • I want to go to INDEL6


The word may be in a position other than the last word of the sentence.



For each sentence (i.e., cell), I want to search for the presence of any word fitting the described letter pattern, and return this word as value.






microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013







share|improve this question









New contributor




ajit maurya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 2




    Heavy vote-down for shouting in all-capitals. Strongly suggest you edit.
    – Michael Harvey
    2 days ago










  • I don't really have a clear picture of your requirement. Are you looking for the presence (true/false) of a "word" (i.e., character string containing only alphanumerics--letters/numbers but no spaces or other kinds of characters), matching a pattern (starts with IN, and ends with 4, 1, 6, or b), which can be located anywhere in a "sentence" but delimited from it by space or punctuation, that is contained in a single cell? Is it always a separate "word" or can it be a character string embedded in a longer "word"? (cont'd)
    – fixer1234
    yesterday










  • Is it always exactly 6 characters? Are the letters always uppercase? What do the "sentences" look like? Can the "word" appear more than once, including as part of a larger word? This would be easier to understand if you include examples of the sentences that represent the range of conditions.
    – fixer1234
    yesterday










  • Also, as Gary's Student interpreted, is the word the only thing in the cell or is it somewhere within other text in a cell (which are very different requirements)?
    – fixer1234
    yesterday










  • I want to search particular pattern in a sentence in a cell. e.g. " My Custom location is INNSA1" OR "I work at INMAA4" or "I want to go to INDEL6"
    – ajit maurya
    yesterday

















up vote
-2
down vote

favorite












I am looking for formula to search for "words" with a particular letter pattern, like INNSA1 or INMAA4 or INPTPB, where the word starts with IN and ends with 4, 1, 6, or B.



A collection of Excel cells each contain a sentence in which one such word may be found. For example:




  • My Custom location is INNSA1

  • I work at INMAA4

  • I want to go to INDEL6


The word may be in a position other than the last word of the sentence.



For each sentence (i.e., cell), I want to search for the presence of any word fitting the described letter pattern, and return this word as value.






microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013







share|improve this question









New contributor




ajit maurya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 2




    Heavy vote-down for shouting in all-capitals. Strongly suggest you edit.
    – Michael Harvey
    2 days ago










  • I don't really have a clear picture of your requirement. Are you looking for the presence (true/false) of a "word" (i.e., character string containing only alphanumerics--letters/numbers but no spaces or other kinds of characters), matching a pattern (starts with IN, and ends with 4, 1, 6, or b), which can be located anywhere in a "sentence" but delimited from it by space or punctuation, that is contained in a single cell? Is it always a separate "word" or can it be a character string embedded in a longer "word"? (cont'd)
    – fixer1234
    yesterday










  • Is it always exactly 6 characters? Are the letters always uppercase? What do the "sentences" look like? Can the "word" appear more than once, including as part of a larger word? This would be easier to understand if you include examples of the sentences that represent the range of conditions.
    – fixer1234
    yesterday










  • Also, as Gary's Student interpreted, is the word the only thing in the cell or is it somewhere within other text in a cell (which are very different requirements)?
    – fixer1234
    yesterday










  • I want to search particular pattern in a sentence in a cell. e.g. " My Custom location is INNSA1" OR "I work at INMAA4" or "I want to go to INDEL6"
    – ajit maurya
    yesterday















up vote
-2
down vote

favorite









up vote
-2
down vote

favorite











I am looking for formula to search for "words" with a particular letter pattern, like INNSA1 or INMAA4 or INPTPB, where the word starts with IN and ends with 4, 1, 6, or B.



A collection of Excel cells each contain a sentence in which one such word may be found. For example:




  • My Custom location is INNSA1

  • I work at INMAA4

  • I want to go to INDEL6


The word may be in a position other than the last word of the sentence.



For each sentence (i.e., cell), I want to search for the presence of any word fitting the described letter pattern, and return this word as value.






microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013







share|improve this question









New contributor




ajit maurya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











I am looking for formula to search for "words" with a particular letter pattern, like INNSA1 or INMAA4 or INPTPB, where the word starts with IN and ends with 4, 1, 6, or B.



A collection of Excel cells each contain a sentence in which one such word may be found. For example:




  • My Custom location is INNSA1

  • I work at INMAA4

  • I want to go to INDEL6


The word may be in a position other than the last word of the sentence.



For each sentence (i.e., cell), I want to search for the presence of any word fitting the described letter pattern, and return this word as value.






microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013




microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013






share|improve this question









New contributor




ajit maurya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




ajit maurya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 21 hours ago









fixer1234

17.3k144280




17.3k144280






New contributor




ajit maurya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 2 days ago









ajit maurya

62




62




New contributor




ajit maurya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





ajit maurya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






ajit maurya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.








  • 2




    Heavy vote-down for shouting in all-capitals. Strongly suggest you edit.
    – Michael Harvey
    2 days ago










  • I don't really have a clear picture of your requirement. Are you looking for the presence (true/false) of a "word" (i.e., character string containing only alphanumerics--letters/numbers but no spaces or other kinds of characters), matching a pattern (starts with IN, and ends with 4, 1, 6, or b), which can be located anywhere in a "sentence" but delimited from it by space or punctuation, that is contained in a single cell? Is it always a separate "word" or can it be a character string embedded in a longer "word"? (cont'd)
    – fixer1234
    yesterday










  • Is it always exactly 6 characters? Are the letters always uppercase? What do the "sentences" look like? Can the "word" appear more than once, including as part of a larger word? This would be easier to understand if you include examples of the sentences that represent the range of conditions.
    – fixer1234
    yesterday










  • Also, as Gary's Student interpreted, is the word the only thing in the cell or is it somewhere within other text in a cell (which are very different requirements)?
    – fixer1234
    yesterday










  • I want to search particular pattern in a sentence in a cell. e.g. " My Custom location is INNSA1" OR "I work at INMAA4" or "I want to go to INDEL6"
    – ajit maurya
    yesterday
















  • 2




    Heavy vote-down for shouting in all-capitals. Strongly suggest you edit.
    – Michael Harvey
    2 days ago










  • I don't really have a clear picture of your requirement. Are you looking for the presence (true/false) of a "word" (i.e., character string containing only alphanumerics--letters/numbers but no spaces or other kinds of characters), matching a pattern (starts with IN, and ends with 4, 1, 6, or b), which can be located anywhere in a "sentence" but delimited from it by space or punctuation, that is contained in a single cell? Is it always a separate "word" or can it be a character string embedded in a longer "word"? (cont'd)
    – fixer1234
    yesterday










  • Is it always exactly 6 characters? Are the letters always uppercase? What do the "sentences" look like? Can the "word" appear more than once, including as part of a larger word? This would be easier to understand if you include examples of the sentences that represent the range of conditions.
    – fixer1234
    yesterday










  • Also, as Gary's Student interpreted, is the word the only thing in the cell or is it somewhere within other text in a cell (which are very different requirements)?
    – fixer1234
    yesterday










  • I want to search particular pattern in a sentence in a cell. e.g. " My Custom location is INNSA1" OR "I work at INMAA4" or "I want to go to INDEL6"
    – ajit maurya
    yesterday










2




2




Heavy vote-down for shouting in all-capitals. Strongly suggest you edit.
– Michael Harvey
2 days ago




Heavy vote-down for shouting in all-capitals. Strongly suggest you edit.
– Michael Harvey
2 days ago












I don't really have a clear picture of your requirement. Are you looking for the presence (true/false) of a "word" (i.e., character string containing only alphanumerics--letters/numbers but no spaces or other kinds of characters), matching a pattern (starts with IN, and ends with 4, 1, 6, or b), which can be located anywhere in a "sentence" but delimited from it by space or punctuation, that is contained in a single cell? Is it always a separate "word" or can it be a character string embedded in a longer "word"? (cont'd)
– fixer1234
yesterday




I don't really have a clear picture of your requirement. Are you looking for the presence (true/false) of a "word" (i.e., character string containing only alphanumerics--letters/numbers but no spaces or other kinds of characters), matching a pattern (starts with IN, and ends with 4, 1, 6, or b), which can be located anywhere in a "sentence" but delimited from it by space or punctuation, that is contained in a single cell? Is it always a separate "word" or can it be a character string embedded in a longer "word"? (cont'd)
– fixer1234
yesterday












Is it always exactly 6 characters? Are the letters always uppercase? What do the "sentences" look like? Can the "word" appear more than once, including as part of a larger word? This would be easier to understand if you include examples of the sentences that represent the range of conditions.
– fixer1234
yesterday




Is it always exactly 6 characters? Are the letters always uppercase? What do the "sentences" look like? Can the "word" appear more than once, including as part of a larger word? This would be easier to understand if you include examples of the sentences that represent the range of conditions.
– fixer1234
yesterday












Also, as Gary's Student interpreted, is the word the only thing in the cell or is it somewhere within other text in a cell (which are very different requirements)?
– fixer1234
yesterday




Also, as Gary's Student interpreted, is the word the only thing in the cell or is it somewhere within other text in a cell (which are very different requirements)?
– fixer1234
yesterday












I want to search particular pattern in a sentence in a cell. e.g. " My Custom location is INNSA1" OR "I work at INMAA4" or "I want to go to INDEL6"
– ajit maurya
yesterday






I want to search particular pattern in a sentence in a cell. e.g. " My Custom location is INNSA1" OR "I work at INMAA4" or "I want to go to INDEL6"
– ajit maurya
yesterday












1 Answer
1






active

oldest

votes

















up vote
0
down vote













This is just an example that you can adapt to your schema.



With data from A2 to A26, in B2 enter:



=IF(AND(LEFT(A2,2)="IN",OR(RIGHT(A2,1)="B",RIGHT(A2,1)="1",RIGHT(A2,1)="4",RIGHT(A2,1)="6")),1+MAX($B$1:B1),"")


and copy downward:



enter image description here



As you see, each item of "good" data is marked with a simple sequential index.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "3"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });






    ajit maurya is a new contributor. Be nice, and check out our Code of Conduct.










     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1376187%2flooking-for-formula-to-search-for-particular-pattern-in-excel%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    0
    down vote













    This is just an example that you can adapt to your schema.



    With data from A2 to A26, in B2 enter:



    =IF(AND(LEFT(A2,2)="IN",OR(RIGHT(A2,1)="B",RIGHT(A2,1)="1",RIGHT(A2,1)="4",RIGHT(A2,1)="6")),1+MAX($B$1:B1),"")


    and copy downward:



    enter image description here



    As you see, each item of "good" data is marked with a simple sequential index.






    share|improve this answer



























      up vote
      0
      down vote













      This is just an example that you can adapt to your schema.



      With data from A2 to A26, in B2 enter:



      =IF(AND(LEFT(A2,2)="IN",OR(RIGHT(A2,1)="B",RIGHT(A2,1)="1",RIGHT(A2,1)="4",RIGHT(A2,1)="6")),1+MAX($B$1:B1),"")


      and copy downward:



      enter image description here



      As you see, each item of "good" data is marked with a simple sequential index.






      share|improve this answer

























        up vote
        0
        down vote










        up vote
        0
        down vote









        This is just an example that you can adapt to your schema.



        With data from A2 to A26, in B2 enter:



        =IF(AND(LEFT(A2,2)="IN",OR(RIGHT(A2,1)="B",RIGHT(A2,1)="1",RIGHT(A2,1)="4",RIGHT(A2,1)="6")),1+MAX($B$1:B1),"")


        and copy downward:



        enter image description here



        As you see, each item of "good" data is marked with a simple sequential index.






        share|improve this answer














        This is just an example that you can adapt to your schema.



        With data from A2 to A26, in B2 enter:



        =IF(AND(LEFT(A2,2)="IN",OR(RIGHT(A2,1)="B",RIGHT(A2,1)="1",RIGHT(A2,1)="4",RIGHT(A2,1)="6")),1+MAX($B$1:B1),"")


        and copy downward:



        enter image description here



        As you see, each item of "good" data is marked with a simple sequential index.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited 2 days ago

























        answered 2 days ago









        Gary's Student

        13.2k31729




        13.2k31729






















            ajit maurya is a new contributor. Be nice, and check out our Code of Conduct.










             

            draft saved


            draft discarded


















            ajit maurya is a new contributor. Be nice, and check out our Code of Conduct.













            ajit maurya is a new contributor. Be nice, and check out our Code of Conduct.












            ajit maurya is a new contributor. Be nice, and check out our Code of Conduct.















             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1376187%2flooking-for-formula-to-search-for-particular-pattern-in-excel%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            Mouse cursor on multiple screens with different PPI

            Image
            24 6 I'm using a 4K display along with a Full HD display with both almost the same physical size and it brings some mouse cursor problems to me working with it on the default Windows settings. As the mouse movement speed depends on the pixels, it's very slow on the 4K display and very fast on the Full HD display. Is there a windows setting or application to move the mouse depending on the visible distance and not the pixels? Another problem is the virtual wall between the screens I can't move the cursor from the upper half of the 4K screen to the Full HD screen: How is it possible to virtually stretch the cursor transition area of the Full HD screen to the one of the 4K monitor? The smallest problem, nevertheless painful, is the cursor size which varies between the screens. Is there a way ...
            Read more

            Agildo Ribeiro

            Image
            Agildo Ribeiro Agildo em 2015 Nome completo Agildo da Gama Barata Ribeiro Filho Nascimento 26 de abril de 1932 Rio de Janeiro, DF Morte 28 de abril de 2018 (86 anos) Rio de Janeiro, RJ Ocupação Ator e humorista ...
            Read more

            Sometime when accessing a menu: “Ubuntu 16.04 has experienced an internal error”

            Image
            0 Sometime, when I try to access some menu (may be inside an application or desktop, doesn't matter), I got the following error. The menu and submenu disappear for a while. After that, I can continue using the system. And the lspci gave me the following output: $ lspci -k | grep -EA3 'VGA|3D|Display' 00:02.0 VGA compatible controller: Intel Corporation Haswell-ULT Integrated Graphics Controller (rev 0b) DeviceName: Onboard IGD Subsystem: Dell Haswell-ULT Integrated Graphics Controller Kernel driver in use: i915 unity compiz error-handling share | improve this question edited Mar 5 at 16:09 ...
            Read more