Formula(s) to extract string that matches certain criteria from a text

**Turtler** · 01-09-2014, 11:49 PM

Hi everyone!

I have the following problem:

I have many different texts which vary widely in their length, structure and so on.
In most of those texts, at a varying position, there is a number with the following criteria:
- starts with "30"
- length is 9 digits

So for example I have the following texts:

"TRSF I-BAK CR 1912/FTSZE/W3251 842000.00 Mka Poluia Sode : 307827482"
"#307827439"
""NA 308729762/M0CP Czh hha"
"ATALA Trans 0700000224499 5111892056353077"

The formula I search for should have the number as its output.

Currently the following formula in place (Texts being in Column B):

Formula:

Please Login or Register  to view this content.

However, it's far away from a real solution, e.g. it does not incorporate the mentioned length of the string and therefore e.g. finds a match in Example 4 even though this is one of the cases where the number is not in the text.

I know that Excel is probably not the best tool for such a task but I hope you guys can help me out

Thanks a lot in advance!

**humdingaling** · 01-09-2014, 11:56 PM

splat a IF around it?

If Len<9 then "not found"?

**dredwolf** · 01-10-2014, 12:01 AM

The reason you get a match in the 4th case is that your search specs are not strict enough, you are searching for "30", which is in the 4th example :
"ATALA Trans 0700000224499 5111892056353077"
therefore, it does match, if you have stricter specifications, please submit a sample workbook so that we can see examples of matches an non-matches

**kvsrinivasamurthy** · 01-10-2014, 02:37 AM

If text starts from A1 put this in B1 and drag down.

Please Login or Register  to view this content.

**Turtler** · 01-10-2014, 03:17 AM

Thanks guys for your replies so far!
I have no idea why I couldn't think of LEN() myself

I attached an example workbook with the original solution, a modified one including the LEN() (#2) and the solution by kvsrinivasamurthy which however does not seem to work.
#2 seems to provide reliable results so far, I'll do a little more testing but it looks promising!

TextExtractExampleWB1.xlsx

**humdingaling** · 01-12-2014, 08:04 PM

Formula:

Please Login or Register  to view this content.

how bout MID instead of left/right/Trim combo

have 2 If loops
one to see if any "30" exist
second to check the length criteria

**martindwilson** · 01-12-2014, 08:40 PM

=IFERROR(MID(A2,SEARCH("30???????",A2),9)+0,"not found") seems to work
but on second look if there is another 30 in the cell all these formulas fail
eg
TRSF I-BAK CR 1912/FTSZE/W3251 302000.00 Mka Poluia Sode : 307827482

**Turtler** · 01-16-2014, 11:59 PM

Thanks again for the further input!

The MID() function seems indeed much easier, simple but very helpful!

And Martin, thank you very much for the formula! I was trying to make it work with wildcards but somehow didn't manage to...
But the problem you mentioned was exactly the one I just encountered with a different formula.
Any further advice how to solve this problem?

**bebo021999** · 01-17-2014, 12:42 AM

This solution works for cases including examples like this:

1) TRSF I-BAK CR 1912/FTSZE/W3251 301234.56 Mka Poluia Sode : 307827482
2) TRSF I-BAK CR 1912/FTSZE/W3251 301234567 Mka Poluia Sode : 307827482
In case 2, there are 2 matchs, take the last.

Please Login or Register  to view this content.

**Turtler** · 01-17-2014, 07:26 AM

Originally Posted by bebo021999

This solution works for cases including examples like this:

1) TRSF I-BAK CR 1912/FTSZE/W3251 301234.56 Mka Poluia Sode : 307827482
2) TRSF I-BAK CR 1912/FTSZE/W3251 301234567 Mka Poluia Sode : 307827482
In case 2, there are 2 matchs, take the last.

Please Login or Register  to view this content.

Thank you very much!
Even though I cannot really say that I understand the formula it works in most of the cases

Would you mind explaining the functionality of this formula?

But for example in the following case:

Please Login or Register  to view this content.

The formula gives the this output: 302543552

However, as I now realize, in basically all the cases there is either a space or a line break before or after the number I search for.
Sorry that I didn't include that above.

So is there a way to ensure that either there is a space/line break before or after the number?

(The formula now also gives out results that do not start with "30" and thus are not the number I search for, however I think that is pretty easy to solve).

Thanks again!

**martindwilson** · 01-17-2014, 09:39 AM

#307827439 has no space before!

**bebo021999** · 01-17-2014, 05:29 PM

Originally Posted by Turtler

Would you mind explaining the functionality of this formula?

=LOOKUP(2,1/(LEN(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),9))=9),--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),9))
Assuming

A2= abc 301234567 def

we need to get 9-digit starts with 30 out

Follow these steps:

1) Go through all the cases of Text in A2 with LEN 9

=MID(A2,ROW(INDIRECT("1:"&LEN(A2))),9)

in which ROW(INDIRECT("1:"&LEN(A2))) establishes array of {1,2,3,...,17}

Highlight the MID then F9 we get:

{"abc 30123";"bc 301234";"c 3012345";" 30123456";"301234567";"01234567 ";"1234567 d";"234567 de";"34567 def";"4567 def";"567 def";"67 def";"7 def";" def";"def";"ef";"f"}

To filter number with: --MID(....)

{#VALUE!;#VALUE!;#VALUE!;30123456;301234567;1234567;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

Now we know the number we need is in the 5th position.

2)To marks the number position with LEN 9 by LEN(MID(...)=9

{#VALUE!;#VALUE!;#VALUE!;FALSE;TRUE;FALSE;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

to exclude FALSE (=0) value, then remains TRUE (=1) only:
1/LEN(...) =

3) Use LOOKUP to get value

=LOOKUP(2,1/LEN(),--MID())

Hope it is clear for you.

**dredwolf** · 01-19-2014, 01:29 AM

As I believe I mentioned before, the more actual rules for the definition of the search text, the more refined the the solution can be, but without some examples of what you want, we are still "guessing" at what you expect...

Please upload a sample so that we can see what you are getting, and what you need !

**Turtler** · 01-21-2014, 08:09 AM

Hey all, thanks so much again!

Martin you're right, there are cases where there is no space, sorry about my mistake.

Bebo, thank you very much for the explanations! They helped a lot!

Dredwolf, sorry if my remarks weren't really clear.
I myself do not really know how the cases in fact look like since they come from 3rd parties, I only know that in some cases there will be this number starting with "30" in there and in some cases not and I'm trying to get this number for the cases were it is included and have a error message where it is not included.
I also uploaded an example workbook in my post from January 10. But I have attached an updated version with more solutions and also the one of Bebo included here: TextExtractExampleWB4.xlsx

So now that a space is not valid for identifying the start of the number, let's assume that there has to be a non-numerical value in front of a number starting with "30" followed by 7 numerical digits, followed by nothing or a non-numerical value.

In know it's quite confusing and you've been a great help already but perhaps someone has an idea on how to further tackle this problem

**bebo021999** · 01-21-2014, 10:43 AM

Updated version:

Please Login or Register  to view this content.

may works?

**Turtler** · 01-27-2014, 01:03 AM

Originally Posted by bebo021999

Updated version:

Please Login or Register  to view this content.

may works?

Thanks again bebo!

Unfortunately, for the last example, the formula still gives out an incorrect result, see Workbook here: Attachment 292798.
I don't know if that is possible (or if you already incorporated that) but can you include the check I described in my previous post in the formula?

non-numerical value in front of a number starting with "30" followed by 7 numerical digits, followed by nothing or a non-numerical value

This is unfortunately beyond my capabilities so I hope one of you guys can help me out!
Thanks again so much for your efforts!

**kvsrinivasamurthy** · 01-27-2014, 05:18 AM

Pl see attached file.

Formula(s) to extract string that matches certain criteria from a text

LinkBack

Thread Tools

Rate This Thread

Display

Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Re: Formula(s) to extract string that matches certain criteria from a text

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Extract text from a string of text (amend formula to include new criteria)

what formula to extract text from a long string?

formula to extract a section of of a text string

Extract Math Formula from text string

[SOLVED] Formula to extract digits from a text string?

Bookmarks

Bookmarks

Posting Permissions