+ Reply to Thread
Results 1 to 7 of 7

Extract Text in middle

  1. #1
    Registered User
    Join Date
    07-31-2019
    Location
    HONG KONG
    MS-Off Ver
    EXCEL 2016
    Posts
    48

    Extract Text in middle

    Hi
    in excel, i have a text in one cell as below.

    (1) ABCDE, (2)FQHIJ, (3)KLMNO, (4)PRQS, (5)TUVWXYZ

    i want the result as below
    ABCDE
    FQHIJ
    KLMNO
    PRQS
    TUVWXYZ

    it seems the formula is to look for the text between "(1)" and the comma ","by using LEFT, MID, FIND. Other cell is text between ""(2)" and the comma and so on.
    But i am still not clear about the formula exactly.

    thanks a lot.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Extract Text in middle

    Hi Wongsiuon,

    See the attached that solves your problem.

    Space Trim for Wongsiuon.xlsx

    The formula has a trick. You make each space 100 spaces in the single string. Then use Mid() to take each 100 characters and use Trim() to remove leading and trailing spaces.

    See if this works for you.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Text in middle

    With your data in A1, try this in B1, copied down...
    =MID(TRIM(MID(SUBSTITUTE($A$1,", ",REPT(" ",LEN(A$1))),LEN($A$1)*(ROW()-1)+1,LEN($A$1))),5,99)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract Text in middle

    Or
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 (1)ABCDE, (2)FQHIJ, (3)KLMNO, (4)PRQS, (5)TUVWXYZ ABCDE
    2 FQHIJ
    3 KLMNO
    4 PRQS
    5 TUVWXYZ
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Extract Text in middle

    Another way:

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A$1,")",","),",",REPT(" ",200)),200*(ROW(A1)*2-1),200))

  6. #6
    Registered User
    Join Date
    07-31-2019
    Location
    HONG KONG
    MS-Off Ver
    EXCEL 2016
    Posts
    48

    Re: Extract Text in middle

    Thanks everyone!!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Text in middle

    Happy to help and thanks for the feedback

+ 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. Extract string of text in the middle of 2 delimiters
    By MoldyBread in forum Excel General
    Replies: 3
    Last Post: 05-07-2018, 07:21 AM
  2. [SOLVED] Extract text from the middle
    By jackson_hollon in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-21-2016, 03:23 PM
  3. how extract phone number from middle of the text
    By perusjosh in forum Excel General
    Replies: 4
    Last Post: 07-07-2015, 11:19 AM
  4. Function that enables me to extract text in the middle of a cell
    By 320 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-26-2014, 07:07 AM
  5. Extract number in the middle of text (check from right-to-left)
    By diywho in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-20-2012, 05:21 AM
  6. Extract text from the middle of a string for concatenation
    By christopherp in forum Excel General
    Replies: 1
    Last Post: 05-16-2011, 08:55 AM
  7. Extract 1, 2 or 3 words from middle of text in a cell
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-31-2010, 02:55 PM

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