formula extract text

[john_cash]

Hello to all.
In column C of the attached table there is text in each cell which is different between rows.
I should extract from these cells the "NUMBER" which is after the word:
ART./ART/Art./COD./COD./cod
until you get to the first letter following this number.
This number is not always the same but can be from 3 digits up to 12/13 digits.
I have highlighted some in red.
The number drawn must be placed in column D.
I hope I have explained.
Thanks
john

[Bernie Deitrick]

Try this in D2, copied down. It fails on a few that don't follow the typical pattern - if enough follow another pattern, you can add that extraction formula with an IFERROR or other conditional

=IFERROR(LEFT(TRIM(MID(C2,FIND(".",C2)+1,LEN(C2))),FIND(" ",TRIM(MID(C2,FIND(".",C2)+1,LEN(C2))))-1),TRIM(MID(C2,FIND(".",C2)+1,LEN(C2))))

[john_cash]

Hi bernie,
your formula works fine, for cells that don't correct I will do it by hand.
A greeting and thanks again.
john

[mehmetcik]

Formula for D2 =LEFT(TRIM(RIGHT(C2,LEN(C2)-IF(ISERROR(SEARCH("art.",C2)),SEARCH("cod.",C2),SEARCH("art.",C2))-3)),MIN(IF(ISERROR(MID(SUBSTITUTE(SUBSTITUTE(E2," ",1),".",1),ROW(INDIRECT("A1:A"&LEN(E2))),1)*1),ROW(INDIRECT("A1:A"&LEN(E2)))-1,LEN(E2))

[john_cash]

Hi mehmetcik,
your formula error #RIF!

[mehmetcik]

Apologies.

Try:

Formula for D2

=LEFT(TRIM(RIGHT(C2,LEN(C2)-IF(ISERROR(SEARCH("art.",C2)),SEARCH("cod.",C2),SEARCH("art.",C2))-3)),MIN(IF(ISERROR(MID(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(C2,LEN(C2)-IF(ISERROR(SEARCH("art.",C2)),SEARCH("cod.",C2),SEARCH("art.",C2))-3))," ",1),".",1),ROW(INDIRECT("A1:A"&LEN(TRIM(RIGHT(C2,LEN(C2)-IF(ISERROR(SEARCH("art.",C2)),SEARCH("cod.",C2),SEARCH("art.",C2))-3))))),1)*1),ROW(INDIRECT("A1:A"&LEN(TRIM(RIGHT(C2,LEN(C2)-IF(ISERROR(SEARCH("art.",C2)),SEARCH("cod.",C2),SEARCH("art.",C2))-3)))))-1,LEN(TRIM(RIGHT(C2,LEN(C2)-IF(ISERROR(SEARCH("art.",C2)),SEARCH("cod.",C2),SEARCH("art.",C2))))))))

[john_cash]

Hi mehmetcik,
i your formula many words remain

[Bernie Deitrick]

You need to array-enter the formula, using Ctrl-Shift-Enter instead of just enter.

[hrlngrv]

This is the sort of text parsing which simply doesn't make sense without regular expressions.

If you're running under Windows, you can use VBScript regular expressions in VBA. I'm attaching a workbook including the VBA code and formulas to parse most of your numbers, which are much more complex than you describe.

The regex user-defined function it uses is meant to provide the same results as LibreOffice Calc's function of the same name (in English).

[john_cash]

Hi bernie dietrich your formala CTRL+SHIFT+ENTER not work, always many words remain

[john_cash]

Hi hrlngrv,
your macro solution works too, thanks.
john