+ Reply to Thread
Results 1 to 16 of 16

extracting text string with 2 options @ end & limited options

  1. #1
    Registered User
    Join Date
    10-23-2010
    Location
    Middle Europe
    MS-Off Ver
    Excel 2003
    Posts
    3

    Thumbs up extracting text string with 2 options @ end & limited options

    If have a similiar task like in this thread (Help extracting a particular string of text from a cell).
    But it is a bit of a harder nut (for me). I try to explain; the content of the Data-Cell could look like this examples (between "" the content, the desired output bold):
    1. "abcxyz" -> "" (Nothing)
    2. "CR 256"
    3. "CR 256, 278, 296"
    4. "CR 256,278,296"
    5. "CR 256; XYZ 412, 478"
    6. "CR 256, 278, 296; XYZ 412, 478"
    7. "ABC 1232, 1468; CR 1256, 1278, 1296"
    8. "ABC 1232, 1468; CR 1256, 1278, 1296; XYZ 412, 478"
    9. "ABC 1232, 1468; CR 1256; XYZ 412, 478"
    Trying to sythesize I like to extract the first number after the String "CR " and before the comma (variant A) or semicolon (variant B).

    I was able to find a solution to extract the string before the semicolon (or end of string) if there is nothing before the string "CR " with this code (working for example 1-2 & 5, working in part for example 3, 4 & 6, not working for example 7-9):
    Please Login or Register  to view this content.

    I hope the masters of Excel code give me a help in this.
    See post # 7 for better outlined examples and limitations.
    Last edited by ChristianR; 10-24-2010 at 06:33 AM. Reason: Title optimizing & Text indications, solved
    I'm using Excel Formulas mainly in the Genealogy Software Ages - Attention: 1) functions are limited - see technical reference link of the used software 2) the lenght of the formula field is limited to 255 characters. I have no extended experience or knowledge.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: extracting a text string with 2 options at the end

    Try this in C1
    Please Login or Register  to view this content.

    This should work if CR is always followed by a space and 3 or 4 digits.

    Hope this helps
    Attached Files Attached Files
    Last edited by Marcol; 10-23-2010 at 02:04 PM. Reason: Forgot Attachment
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: extracting a text string with 2 options at the end

    Mine is little bit longer than yours)))

    =IF(ISERROR(FIND("CR",A1)),"",VALUE(REPLACE(MID(MID(A1,FIND("CR",A1),255)&" ",4,SEARCH(" ",MID(A1,FIND("CR",A1),255)&" ")),SEARCH(" ",MID(MID(A1,FIND("CR",A1),255)&" ",4,SEARCH(" ",MID(A1,FIND("CR",A1),255)&" ")))-1,255,"")))
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: extracting a text string with 2 options at the end

    Ahhhh....., but mine seems to work allowing for it's shortages,... , longer isn't always best...

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

    Re: extracting a text string with 2 options at the end

    Or this:

    =IF(ISNUMBER(FIND("CR ",B2)),MID(B2;FIND("CR ",B2)+3;MIN(FIND(",",RIGHT(B2,LEN(B2)-FIND("CR ",B2)-2)&","),FIND(";",RIGHT(B2,LEN(B2)-FIND("CR ",B2)-2)&";"))-1),"")

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: extracting a text string with 2 options at the end

    Hmmm?

    Have a look at this workbook, I haven't developed my original formula as ChristianR has not responded as yet.
    Beer.. Burpp....
    Attached Files Attached Files
    Last edited by Marcol; 10-23-2010 at 07:33 PM.

  7. #7
    Registered User
    Join Date
    10-23-2010
    Location
    Middle Europe
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: extracting a text string with 2 options at the end

    Wow. Thank you for your posts and solutions. Especially Marcol for the Excel example file. Forgive me: I forgot to add some crucial notes:
    1. the searched string could be from 1 to x characters (theoretically infinite, but practical 1 to 20 characters); the output must not be a number
    2. the usable functions are limited - see technical reference link of the used software
    3. the lenght of the formula field is limited to 255 characters; please note also that I have to use the variable "IDNO.TYPE" at the place of B1/A1/B2 and stayin in the 255 chars limit
    4. the Cell could be also empty
    For reason 1. Marcol's solution is not suited.
    For reason 2. (& in part 4.) zbor's otherwise perfect working solution is not usable in the used genealogy-software, because the function MIN is not supported
    For reason 3. contaminated's solution is too long

    Hmm; it is sad: maybe I ask the software autor to add the MIN function; but this can take months/years to find the way in a new software version. But maybe there is another way to reach the goal

    Here are the updated example list and XLS-file:
    1. "" -> "" (Nothing)
    2. "abcxyz" -> "" (Nothing)
    3. "CR 256"
    4. "CR 256, 278, 296"
    5. "CR 2,278,296"
    6. "CR 25; XYZ 412, 478"
    7. "CR 2566, 278, 296; XYZ 412, 478"
    8. "ABC 1232, 1468; CR 12345, 1278, 1296"
    9. "ABC 1232, 1468; CR 12345678, 1278, 1296; XYZ 412, 478"
    10. "ABC 1232, 1468; CR 12(a)b.56+II-VI; XYZ 412, 478"
    11. "ABC 1232, 1468; CR 123456789012; XYZ 412, 478"
    Attached Files Attached Files
    Last edited by ChristianR; 10-23-2010 at 08:46 PM.

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: extracting text string with 2 options @ end & limited options

    Have a look at the attached workbook, it shows a few ideas.

    Given the restrictions imposed by your software, (it's a bit surprising that MAX and MIN are not supported), I think this might be usable
    Please Login or Register  to view this content.

    Hope this helps
    Attached Files Attached Files

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

    Re: extracting text string with 2 options @ end & limited options

    I'll look for solution without MIN function but before that here are some notes:

    In D column use *1 inside formula... Let me explain:

    =IF(ISNUMBER(A1), A1, "Not number")

    OK.. So this formula will return you A1 if A1 is number and Not number if it's not.

    Now, if you multiply it *1 =IF(ISNUMBER(A1), A1, "Not number")*1

    You'll get:

    a) if A1 is number (i.e. 100) you'll get 100*1 = 100
    b) if A1 is not number (i.e. ABC) you'll get ABC*1 = #VALUE! due to multiplying not number...

    So you should rather do this:

    =IF(ISNUMBER(A1), A1*1, "Not number")

    which will give you 100 in first case and Not number in second rather than #VALUE!


    So, formula should be:

    =IF(ISNUMBER(FIND("CR ";B2));1*MID(B2;FIND("CR ";B2)+3;MIN(FIND(",";RIGHT(B2;LEN(B2)-FIND("CR ";B2)-2)&",");FIND(";";RIGHT(B2;LEN(B2)-FIND("CR ";B2)-2)&";"))-1);"")

    (ignore ; delimiter due to regional setings)

    Also note that 12(a)b.56+II-VI will return you #VALUE! in any case because it's not a number... If you want this result then you need to remove 1*

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

    Re: extracting text string with 2 options @ end & limited options

    Here, without MIN:

    =SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(B2, "CR ", REPT(" ", 100)), 95, 100), ",", REPT(" ", 100)), ";", REPT(" ";100)), 100), " ";"")

    But keep in mind comment from above that 1* will give you error in 11th row
    Attached Files Attached Files

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

    Re: extracting text string with 2 options @ end & limited options

    If hundred is not enough for you you can extend it:

    =SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(B2;"CR ";REPT(" ";255));250;255);",";REPT(" ";255));";";REPT(" ";255));255);" ";"")

  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: extracting text string with 2 options @ end & limited options

    Hi zbor have a look at the attachment in post#8.

    I'm not the best with formula, but given the restrictions, it's the best I can offer.

    I'd appreciate your input.

    Also look at this "see technical reference link of the used software" the link is posted at #7

    Cheers
    Alistair

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

    Re: extracting text string with 2 options @ end & limited options

    Hi Marcol,

    I've saw your solution but after I've post it up...

    I agree with you, additional column is good solution for any lenght because it can be easily done...

    Also, mine last formula Post #11) works for ANY number... Not bounded with 102 digits And it's pritty elegant

  14. #14
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: extracting text string with 2 options @ end & limited options

    Yes a bit of crossed wires here!!!

    The software that ChristianR is using doesn't recognise the semi-colon as a deliminator as I understand it,......maybe it does.
    From the handbook
    Please note: Ages! will not understand the localized (German or otherwise) function names, numbers have to use the English format ( pi=3.14 , not 3,14 )
    I have updated the workbook with your latest formula with comma delimiters.
    Hope you don't mind.

    ChristianR zbors' solution would be the way to go!!!
    Attached Files Attached Files
    Last edited by Marcol; 10-24-2010 at 05:06 AM.

  15. #15
    Registered User
    Join Date
    10-23-2010
    Location
    Middle Europe
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: extracting text string with 2 options @ end & limited options

    Wow again! Exceptional support here!!! Here are the wizards of Excel code. How many ways to find a solution. zbor's updated formula is wonderful: so short and doing perfectly the job! I thank you so much!!! I have changed the topic to SOLVED and given you two (zbor and Marcol) reputation.
    I'm tyring to understand zbor's updated formula; here is a formatted version, if some other interested user comes here to this thread:
    Please Login or Register  to view this content.
    Last edited by ChristianR; 10-24-2010 at 07:00 AM.

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

    Re: extracting text string with 2 options @ end & limited options

    Thanks Christian...
    Hope we'll see you here with more challenges
    This was fun
    Best way to understand frmula would be to Evaluate formula in Formula tab.
    But if that doesn't help I can give you further explanation.

    Thanks also to Marcol for pushing the little rock from the top of the mountain and giving me a avalanche of ideas
    Last edited by zbor; 10-24-2010 at 07:01 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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