+ Reply to Thread
Results 1 to 12 of 12

Extract Text from the Text

  1. #1
    Registered User
    Join Date
    01-07-2016
    Location
    Delhi
    MS-Off Ver
    2007
    Posts
    10

    Extract Text from the Text

    I want to extract 600x12x1134 from the below line:-

    Development Fee @ Rs.600/- p.m. for 1134 students (600x12x1134)


    and then multiply these numbers and put in another cell:-

    =RIGHT(B48,LEN(TRIM(B48))-FIND("(",B48))

    My RESULT IS 600x12x1134)

    Can you please correct me in formula????

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Extract Text from the Text

    Based on your solution:

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

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Extract Text from the Text

    Or Try

    =REPLACE(RIGHT(B48,LEN(TRIM(B48))-FIND("(",B48)),FIND(")",RIGHT(B48,LEN(TRIM(B48))-FIND("(",B48))),1,"")

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Extract Text from the Text

    You said you wanted to multiply those numbers. If so, then you'll need to separate them.
    Try these:-
    C48: =--MID(B48,FIND("(",B48)+1,FIND("x",B48)-FIND("(",B48)-1)
    D48: =--MID(B48,FIND("x",B48)+1,FIND("x",B48,FIND("x",B48)+1)-FIND("x",B48)-1)
    E48: =--MID(B48,FIND("x",B48,FIND("x",B48)+1)+1,LEN(B48)-FIND("x",B48,FIND("x",B48)+1)-1)

  5. #5
    Registered User
    Join Date
    01-07-2016
    Location
    Delhi
    MS-Off Ver
    2007
    Posts
    10

    Re: Extract Text from the Text

    Thanks all of you its working now using substitute : -
    =SUBSTITUTE(RIGHT(A1,LEN(TRIM(A1))-FIND("(",A1)),")","")

    But now if space is there at the end

    Development Fee @ Rs.600/- p.m. for 1134 students (6100x112x11139)
    in this case my result is 100x112x11139)

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Extract Text from the Text

    Here, for all solutions.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It will work also if you have more brackets in the text like:
    Development Fee @ (Rs.600/)- p.m. for 1134 students*(6100x112x11139)*

  7. #7
    Registered User
    Join Date
    01-07-2016
    Location
    Delhi
    MS-Off Ver
    2007
    Posts
    10

    Re: Extract Text from the Text

    THANKS zbor ur formula is working in all the cases....but it goes beyond my reach...

    ONE HURDLE IS SOLVED....
    Second hurdle:-
    I want to split these numbers using x sign , MULTIPLY THESE NUMBERS and put the result in another cell....Is that possible using the formula or I have to used the module???
    Last edited by soniakhurana22; 01-08-2016 at 09:45 AM.

  8. #8
    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 from the Text

    Here is another way

    to get string from the parenthesis:

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


    To multiply the results

    use this array formula

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


    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER



    B
    C
    D
    47
    Text From the parenthesis Multiplied results
    48
    Development Fee @ Rs.600/- p.m. for 1134 students (600x12x1134) 600x12x1134
    8164800
    49
    Development Fee @ Rs.600/- p.m. for 1134 students* (6100x112x11139)* 6100x112x11139
    7610164800
    Sheet: Sheet1
    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

  9. #9
    Registered User
    Join Date
    01-07-2016
    Location
    Delhi
    MS-Off Ver
    2007
    Posts
    10

    Re: Extract Text from the Text

    Thanks all of you EXCEL EXPERTS....

    I just want to ask one simple Question...Array formula is working correctly...I am applying this same array formula on locked file....MESSAGE IS coming when Pressing CTRL+SHIFT+ENTER..The specified formula cannot be entered because it used more levels of nesting that are allowed in the current file format.

    What is the reason on locked file , why this array formula cannot be applied??
    Last edited by soniakhurana22; 01-09-2016 at 08:48 AM.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Extract Text from the Text

    There is no need to highlight any of your thread: we are quite capable of reading normal text.

  11. #11
    Registered User
    Join Date
    01-09-2016
    Location
    Delhi
    MS-Off Ver
    2007
    Posts
    6

    Re: Extract Text from the Text

    Save as Excel Workbook.

  12. #12
    Registered User
    Join Date
    01-07-2016
    Location
    Delhi
    MS-Off Ver
    2007
    Posts
    10

    Re: Extract Text from the Text

    Its done....I click on File Menu--Save as-->Save the file as Excel Workbook(Default Format).Now I am able to apply the array formula.

    Lots of thanks to all who helped me in solving my query.

+ 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. 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
  2. Replies: 2
    Last Post: 05-13-2015, 06:52 AM
  3. Extract text between two characters in a string - varing text length
    By luv2birdie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-04-2014, 06:10 PM
  4. Replies: 4
    Last Post: 08-13-2014, 11:03 PM
  5. [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
  6. [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
  7. [SOLVED] EXTRACT TEXT FROM TEXT STRING:The names are of variable length
    By carricka in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-08-2005, 06:05 AM

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