+ Reply to Thread
Results 1 to 16 of 16

Extract text

  1. #1
    Registered User
    Join Date
    02-29-2020
    Location
    Malaysia
    MS-Off Ver
    Office 365
    Posts
    85

    Extract text

    Hi,

    I want the formula to extract text in between "-C-" (somestimes also can be "-L-", example in cell A7 and A8) and "_"
    For those result that have 4 characters, stroke "/" will insert in the middle of the text. Example please refer in cell B6 and B7

    Thanks.
    Attached Files Attached Files
    Last edited by babychai; 08-07-2021 at 11:23 AM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,938

    Re: Extract text

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

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,145

    Re: Extract text

    I could do it in one (long) formula, but it would be more efficient to use a helper column for the intermediate result - is this acceptable?

    Pete

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,393

    Re: Extract text

    Please try

    =TEXT(TRIM(RIGHT(SUBSTITUTE(LEFT(A3:A8,FIND("_",A3:A8)-1),"-",REPT(" ",9)),9)),"[>999]00\/00;0")
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Extract text

    Power Query is also an option.
    Attached Files Attached Files
    Messages have been translated from Dutch to English by means of google translate.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,145

    Re: Extract text

    Here's another one for B3:

    =TEXT(MID(A3,IFERROR(SEARCH("-c-",A3),SEARCH("-L-",A3))+3,SEARCH("_",A3)-IFERROR(SEARCH("-c-",A3),SEARCH("-L-",A3))-3),"[>999]00\/00;0")

    Copy down as required.

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    02-29-2020
    Location
    Malaysia
    MS-Off Ver
    Office 365
    Posts
    85

    Re: Extract text

    Hi All,

    All formula are working great but I don't understand meaning of "[>999]00\/00;0" this format text. Mind to elaborate?

    Thanks.
    Last edited by davesexcel; 08-21-2021 at 10:22 AM.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,145

    Re: Extract text

    It is a Custom Format, where you can specify a simple condition - in this case it is greater than 999 (meaning a 4-digit number). So, if it is a 4-digit number then use the format string 00/00, otherwise use 0.

    Hope this helps.

    Pete

    By the way, if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,938

    Re: Extract text

    All formula are working great
    Glad to help & thanks for the feedback.

  10. #10
    Registered User
    Join Date
    02-29-2020
    Location
    Malaysia
    MS-Off Ver
    Office 365
    Posts
    85

    Re: Extract text

    Pete,

    Thanks for your explanation. May I know purpose for backward slash "\" ?
    I saw the difference if i removed "\" but still don't get the meaning of it.

    Thanks.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,145

    Re: Extract text

    It is basically an "escape" character, meaning to take the following character literally, in this case the forward-slash.

    Hope this helps.

    Pete

  12. #12
    Registered User
    Join Date
    02-29-2020
    Location
    Malaysia
    MS-Off Ver
    Office 365
    Posts
    85

    Re: Extract text

    Pete, got it.

    Thanks a lot for your explanation and thanks for all those help me. Have a good day ahead.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,145

    Re: Extract text

    You're welcome - thanks for the rep.

    Pete

  14. #14
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    EXCEL 2007/365
    Posts
    1,451

    Re: Extract text

    Cell B3 formula , drag down

    HTML Code: 
    =TEXT(TRIM(RIGHT(SUBSTITUTE(LEFT(A3,FIND("_",A3)-1),"-","        "),8)),"[>999]#\/##")

  15. #15
    Registered User
    Join Date
    02-29-2020
    Location
    Malaysia
    MS-Off Ver
    Office 365
    Posts
    85

    Re: Extract text

    wk9128, working well. thanks for your alternative idea.

  16. #16
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    EXCEL 2007/365
    Posts
    1,451

    Re: Extract text

    You're Welcome. Thank You 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. [SOLVED] extract text form text string( extract 5 charactors in front of all left parenthese)
    By happyexcel2021 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2021, 06:05 PM
  2. Replies: 9
    Last Post: 08-20-2020, 05:30 AM
  3. Extract text between two words in a text file and paste to Excel
    By rasull13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2018, 03:38 PM
  4. VBA to open saved web html pages - extract text - paste text within individual cell
    By EddieRubi in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 07-23-2015, 01:43 PM
  5. Replies: 4
    Last Post: 08-13-2014, 11:03 PM
  6. [SOLVED] Extract text from a given point in a text string, when data points do not share the given
    By reedersketer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2014, 03:57 PM
  7. [SOLVED] Extract text from a string of text (amend formula to include new criteria)
    By robertguy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2013, 04:53 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