+ Reply to Thread
Results 1 to 5 of 5

Extract text from cells with mixed characters

  1. 07-15-2014, 11:25 AM #1
    sa02000
    sa02000 is offline
    Forum Contributor
    Join Date
    10-03-2005
    Posts
    185

    Extract text from cells with mixed characters

    Hi - I have a need to extract any text characters towards the right in all cells. What makes it complicated is cells have numbers, curley brackets, date, letters etc. in these cells. Ofcourse I have thousands of rows that I need to do this for which is why I am looking for a formula or a macro to do this.

    I noticed some trends - there are always 10 curley brackets and then some numbers / colon etc. before the text that I need to extract. In some cases there is not numbers / colon etc. between last curley bracket and text that I need to extract.

    I think if there is a way to figure out 10th curley bracket from left and then extract any alphabet character to the right of that curley bracket but before any other curley bracket further to right, that will solve my problem.

    Attached file below shows sample input and output columns.

    Thank you very much in advance,
    Jay
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-15-2014, 11:35 AM #2
    Speshul
    Speshul is offline
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Extract text from cells with mixed characters

    How many phrases are you looking for? Are there only a couple of options? If so:

    Please Login or Register  to view this content.
    *Note
    The last line can be replaced by another copy of the BOLD section of that formula. Just change the two strings to match what you are looking for, and what you want returned respectively. At the very end, the last value_if_true (currently "See Note*"), just put "" and it will blank if it doesn't find any of the strings.

    This is only really viable if you don't have too many possible search terms though.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.
    Reply With QuoteRegister To Reply
  3. 07-15-2014, 02:25 PM #3
    sa02000
    sa02000 is offline
    Forum Contributor
    Join Date
    10-03-2005
    Posts
    185

    Re: Extract text from cells with mixed characters

    Hi Speshul - Thank you for your solution. I think that would work perfectly if those were the only two text strings I had. I had put those in the file as example but actual data has all different kinds of test strings. Is it possible to use characters (Char) to find alphabets after 10th curley bracket and get all alphabets after 10th curley bracket but before any other curley bracket after that?

    Regards,
    Jay
    Reply With QuoteRegister To Reply
  4. 07-15-2014, 02:31 PM #4
    Speshul
    Speshul is offline
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Extract text from cells with mixed characters

    Line 7 in your sample has the text after the 8th curley though

    What you could do is find the first occurance of SPACE (" ") and copy all words after it. You could probably pull the word directly before the space as well if there is always a } before words


    Edit;
    Nevermind, there are spaces within the data as well. Tricky! I'll keep looking.
    Last edited by Speshul; 07-15-2014 at 02:36 PM.
    Reply With QuoteRegister To Reply
  5. 07-15-2014, 02:57 PM #5
    Speshul
    Speshul is offline
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Extract text from cells with mixed characters

    Well this is absurd, but

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A7,"M",""),"D",""),"T",""),"%",""),"SU",""),"BP",""),"PA",""),"TD",""),"CR",""),"-",""),".",""),":",""),"{",""),"}",""),9,""),8,""),7,""),6,""),5,""),4,""),3,""),2,""),1,""),0,"")

    Might lose a couple letters!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Remove Text from Cell with Mixed Characters, Sum Remaining Numbers
    By eugeniusjr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-28-2013, 12:08 AM
  2. Extract Text characters only from a cell
    By judasdac in forum Excel General
    Replies: 2
    Last Post: 05-06-2009, 12:22 PM
  3. extract text separated by characters
    By RANDYC1152 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-25-2009, 09:54 PM
  4. [SOLVED] Extract just numeric part of mixed text/number entry?
    By Heidi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-01-2006, 02:35 PM
  5. Subdividing a Mixed String into Separate Cells Containing only Numbers or Characters
    By VAIBHAV in forum Tips and Tutorials
    Replies: 2
    Last Post: 08-23-2005, 03:49 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1