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):

=TRIM(MID(Cell,FIND("CR ",Cell)+3,FIND(";",Cell&";")-FIND("CR ",Cell)-3))

I hope the masters of Excel code give me a help in this.
See post # 7 for better outlined examples and limitations.

Try this in C1

=IF(ISERROR(FIND("CR ",B1,1)),"",TRIM(MID(SUBSTITUTE(SUBSTITUTE(B1,";",""),","," "),FIND("CR ",SUBSTITUTE(SUBSTITUTE(B1,";",""),","," "),1)+LEN("CR "),4))*1)

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

Hope this helps

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,"")))

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),"")

Hmmm?

Have a look at this workbook, I haven't developed my original formula as ChristianR has not responded as yet.
Beer.. Burpp....

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"

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

=IF(ISERROR(FIND("CR",B2,1)),"",IF(ISERROR(SEARCH(",",B2)),SUBSTITUTE(MID(B2,FIND("CR",B2,1)+3,99),";",","),LEFT(SUBSTITUTE(MID(B2,FIND("CR",B2,1)+3,99),";",","),SEARCH(",",SUBSTITUTE(MID(B2,FIND("CR",B2,1)+3,99),";",","))-1)))

Hope this helps

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*

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

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);" ";"")

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

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!!!

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:

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

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