How do I extract numbers between strings in Excel?












0















Let's say I have cells of data that look like this:



Sample text abc (10) and sample text defg (14)


Test 123 abc (14) and more test defg (17)
I would like to extract the numbers in parentheses after abc and the numbers after defg. How would I go about this? I've tried a couple formulas but they get stuck when it encounters a cell with multiple parentheses.




microsoft-excel







share|improve this question

























  • How do you want the extracted numbers to be displayed? As a single number like 1014 or separated as 10 14 in a single cell?

    – Mark Fitzgerald
    Jan 10 at 0:57











  • Does abc refer to both abc locations in the 2nd example or just the first? On Mark's question, do you want the outputs in separate cells? Are numbers in parentheses only after abc or defg, or can they follow other letter groups? If the latter, do you want the ones only following those two strings? Can a cell contain only one of the strings or none of the strings, or will every cell contain both? Can those strings be in any order? Must the extracted numbers be in the same order as they appear in the cell? Are the numbers always exactly two digits?

    – fixer1234
    Jan 10 at 3:40











  • I would like the numbers extracted in separate cells. So in this example, I'd want to create a column that has what is in parentheses after abc, so 10 and 14, and a different column that has what's in the parentheses after defg, so 14 and 17. I've edited my question as it is unlikely I'll have abc or defg more than once in a cell. The numbers in parentheses will be referring to minutes, so the numbers won't always be exactly 2 digits.

    – Soum
    Jan 10 at 16:58











  • It is possible that there will be other numbers in the cell, but I only want to extract the ones after those two strings. The strings may be in any order and the extracted number must be in the same order that they appear in the cell. The logic I was trying to follow in my mind was to have a function that trims everything before the string "abc (" and then everything after the first ")" and likewise for the defg string.

    – Soum
    Jan 10 at 17:01











  • Could you provide a sample or screenshot about your problem?

    – Lee
    Jan 11 at 1:36
















0















Let's say I have cells of data that look like this:



Sample text abc (10) and sample text defg (14)


Test 123 abc (14) and more test defg (17)
I would like to extract the numbers in parentheses after abc and the numbers after defg. How would I go about this? I've tried a couple formulas but they get stuck when it encounters a cell with multiple parentheses.




microsoft-excel







share|improve this question

























  • How do you want the extracted numbers to be displayed? As a single number like 1014 or separated as 10 14 in a single cell?

    – Mark Fitzgerald
    Jan 10 at 0:57











  • Does abc refer to both abc locations in the 2nd example or just the first? On Mark's question, do you want the outputs in separate cells? Are numbers in parentheses only after abc or defg, or can they follow other letter groups? If the latter, do you want the ones only following those two strings? Can a cell contain only one of the strings or none of the strings, or will every cell contain both? Can those strings be in any order? Must the extracted numbers be in the same order as they appear in the cell? Are the numbers always exactly two digits?

    – fixer1234
    Jan 10 at 3:40











  • I would like the numbers extracted in separate cells. So in this example, I'd want to create a column that has what is in parentheses after abc, so 10 and 14, and a different column that has what's in the parentheses after defg, so 14 and 17. I've edited my question as it is unlikely I'll have abc or defg more than once in a cell. The numbers in parentheses will be referring to minutes, so the numbers won't always be exactly 2 digits.

    – Soum
    Jan 10 at 16:58











  • It is possible that there will be other numbers in the cell, but I only want to extract the ones after those two strings. The strings may be in any order and the extracted number must be in the same order that they appear in the cell. The logic I was trying to follow in my mind was to have a function that trims everything before the string "abc (" and then everything after the first ")" and likewise for the defg string.

    – Soum
    Jan 10 at 17:01











  • Could you provide a sample or screenshot about your problem?

    – Lee
    Jan 11 at 1:36














0












0








0








Let's say I have cells of data that look like this:



Sample text abc (10) and sample text defg (14)


Test 123 abc (14) and more test defg (17)
I would like to extract the numbers in parentheses after abc and the numbers after defg. How would I go about this? I've tried a couple formulas but they get stuck when it encounters a cell with multiple parentheses.




microsoft-excel







share|improve this question
















Let's say I have cells of data that look like this:



Sample text abc (10) and sample text defg (14)


Test 123 abc (14) and more test defg (17)
I would like to extract the numbers in parentheses after abc and the numbers after defg. How would I go about this? I've tried a couple formulas but they get stuck when it encounters a cell with multiple parentheses.




microsoft-excel




microsoft-excel






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 10 at 17:17







Soum

















asked Jan 9 at 23:03









SoumSoum

11




11













  • How do you want the extracted numbers to be displayed? As a single number like 1014 or separated as 10 14 in a single cell?

    – Mark Fitzgerald
    Jan 10 at 0:57











  • Does abc refer to both abc locations in the 2nd example or just the first? On Mark's question, do you want the outputs in separate cells? Are numbers in parentheses only after abc or defg, or can they follow other letter groups? If the latter, do you want the ones only following those two strings? Can a cell contain only one of the strings or none of the strings, or will every cell contain both? Can those strings be in any order? Must the extracted numbers be in the same order as they appear in the cell? Are the numbers always exactly two digits?

    – fixer1234
    Jan 10 at 3:40











  • I would like the numbers extracted in separate cells. So in this example, I'd want to create a column that has what is in parentheses after abc, so 10 and 14, and a different column that has what's in the parentheses after defg, so 14 and 17. I've edited my question as it is unlikely I'll have abc or defg more than once in a cell. The numbers in parentheses will be referring to minutes, so the numbers won't always be exactly 2 digits.

    – Soum
    Jan 10 at 16:58











  • It is possible that there will be other numbers in the cell, but I only want to extract the ones after those two strings. The strings may be in any order and the extracted number must be in the same order that they appear in the cell. The logic I was trying to follow in my mind was to have a function that trims everything before the string "abc (" and then everything after the first ")" and likewise for the defg string.

    – Soum
    Jan 10 at 17:01











  • Could you provide a sample or screenshot about your problem?

    – Lee
    Jan 11 at 1:36



















  • How do you want the extracted numbers to be displayed? As a single number like 1014 or separated as 10 14 in a single cell?

    – Mark Fitzgerald
    Jan 10 at 0:57











  • Does abc refer to both abc locations in the 2nd example or just the first? On Mark's question, do you want the outputs in separate cells? Are numbers in parentheses only after abc or defg, or can they follow other letter groups? If the latter, do you want the ones only following those two strings? Can a cell contain only one of the strings or none of the strings, or will every cell contain both? Can those strings be in any order? Must the extracted numbers be in the same order as they appear in the cell? Are the numbers always exactly two digits?

    – fixer1234
    Jan 10 at 3:40











  • I would like the numbers extracted in separate cells. So in this example, I'd want to create a column that has what is in parentheses after abc, so 10 and 14, and a different column that has what's in the parentheses after defg, so 14 and 17. I've edited my question as it is unlikely I'll have abc or defg more than once in a cell. The numbers in parentheses will be referring to minutes, so the numbers won't always be exactly 2 digits.

    – Soum
    Jan 10 at 16:58











  • It is possible that there will be other numbers in the cell, but I only want to extract the ones after those two strings. The strings may be in any order and the extracted number must be in the same order that they appear in the cell. The logic I was trying to follow in my mind was to have a function that trims everything before the string "abc (" and then everything after the first ")" and likewise for the defg string.

    – Soum
    Jan 10 at 17:01











  • Could you provide a sample or screenshot about your problem?

    – Lee
    Jan 11 at 1:36

















How do you want the extracted numbers to be displayed? As a single number like 1014 or separated as 10 14 in a single cell?

– Mark Fitzgerald
Jan 10 at 0:57





How do you want the extracted numbers to be displayed? As a single number like 1014 or separated as 10 14 in a single cell?

– Mark Fitzgerald
Jan 10 at 0:57













Does abc refer to both abc locations in the 2nd example or just the first? On Mark's question, do you want the outputs in separate cells? Are numbers in parentheses only after abc or defg, or can they follow other letter groups? If the latter, do you want the ones only following those two strings? Can a cell contain only one of the strings or none of the strings, or will every cell contain both? Can those strings be in any order? Must the extracted numbers be in the same order as they appear in the cell? Are the numbers always exactly two digits?

– fixer1234
Jan 10 at 3:40





Does abc refer to both abc locations in the 2nd example or just the first? On Mark's question, do you want the outputs in separate cells? Are numbers in parentheses only after abc or defg, or can they follow other letter groups? If the latter, do you want the ones only following those two strings? Can a cell contain only one of the strings or none of the strings, or will every cell contain both? Can those strings be in any order? Must the extracted numbers be in the same order as they appear in the cell? Are the numbers always exactly two digits?

– fixer1234
Jan 10 at 3:40













I would like the numbers extracted in separate cells. So in this example, I'd want to create a column that has what is in parentheses after abc, so 10 and 14, and a different column that has what's in the parentheses after defg, so 14 and 17. I've edited my question as it is unlikely I'll have abc or defg more than once in a cell. The numbers in parentheses will be referring to minutes, so the numbers won't always be exactly 2 digits.

– Soum
Jan 10 at 16:58





I would like the numbers extracted in separate cells. So in this example, I'd want to create a column that has what is in parentheses after abc, so 10 and 14, and a different column that has what's in the parentheses after defg, so 14 and 17. I've edited my question as it is unlikely I'll have abc or defg more than once in a cell. The numbers in parentheses will be referring to minutes, so the numbers won't always be exactly 2 digits.

– Soum
Jan 10 at 16:58













It is possible that there will be other numbers in the cell, but I only want to extract the ones after those two strings. The strings may be in any order and the extracted number must be in the same order that they appear in the cell. The logic I was trying to follow in my mind was to have a function that trims everything before the string "abc (" and then everything after the first ")" and likewise for the defg string.

– Soum
Jan 10 at 17:01





It is possible that there will be other numbers in the cell, but I only want to extract the ones after those two strings. The strings may be in any order and the extracted number must be in the same order that they appear in the cell. The logic I was trying to follow in my mind was to have a function that trims everything before the string "abc (" and then everything after the first ")" and likewise for the defg string.

– Soum
Jan 10 at 17:01













Could you provide a sample or screenshot about your problem?

– Lee
Jan 11 at 1:36





Could you provide a sample or screenshot about your problem?

– Lee
Jan 11 at 1:36










1 Answer
1






active

oldest

votes


















0














Use nested Search() or Find() functions to get to second and third instances of a character.



cell 1 result:



=MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)


cell 2 result:



MID(A1,SEARCH("(",A1,SEARCH("(",A1)+1)+1,SEARCH(")",A1,SEARCH(")",A1)+1)-SEARCH("(",A1,SEARCH("(",A1)+1)-1)





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',
    autoActivateHeartbeat: false,
    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
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1392495%2fhow-do-i-extract-numbers-between-strings-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









    0














    Use nested Search() or Find() functions to get to second and third instances of a character.



    cell 1 result:



    =MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)


    cell 2 result:



    MID(A1,SEARCH("(",A1,SEARCH("(",A1)+1)+1,SEARCH(")",A1,SEARCH(")",A1)+1)-SEARCH("(",A1,SEARCH("(",A1)+1)-1)





    share|improve this answer






























      0














      Use nested Search() or Find() functions to get to second and third instances of a character.



      cell 1 result:



      =MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)


      cell 2 result:



      MID(A1,SEARCH("(",A1,SEARCH("(",A1)+1)+1,SEARCH(")",A1,SEARCH(")",A1)+1)-SEARCH("(",A1,SEARCH("(",A1)+1)-1)





      share|improve this answer




























        0












        0








        0







        Use nested Search() or Find() functions to get to second and third instances of a character.



        cell 1 result:



        =MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)


        cell 2 result:



        MID(A1,SEARCH("(",A1,SEARCH("(",A1)+1)+1,SEARCH(")",A1,SEARCH(")",A1)+1)-SEARCH("(",A1,SEARCH("(",A1)+1)-1)





        share|improve this answer















        Use nested Search() or Find() functions to get to second and third instances of a character.



        cell 1 result:



        =MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)


        cell 2 result:



        MID(A1,SEARCH("(",A1,SEARCH("(",A1)+1)+1,SEARCH(")",A1,SEARCH(")",A1)+1)-SEARCH("(",A1,SEARCH("(",A1)+1)-1)






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Jan 10 at 23:31

























        answered Jan 9 at 23:39









        BrianBrian

        1895




        1895






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Super User!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1392495%2fhow-do-i-extract-numbers-between-strings-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