Cell contains specific pattern. - Wildcards in find?

[szita2000]

Hi guys.

I have a database, with text strings in Column A.

Here is a 4 row sample of the data:

1.001 v08 - Able to develop Quality Manual in compliance to ISO and Corporate Requirements

1.001 v08 - Aware about Quality Management Manual

Ismeri a molding termék kiengedésének menetét - SUC 1.134 v02 TPL3.2 v01

Ismeri a raktári lerakodás és bevételezés teljes folyamatát (SOP 1.032 v03)

These are text strings where I need the number from the text #.### and the version number

I was trying to extract the numbers and then the version numbers after them but I was unable to find a formula that will handle wildcards.

I tried MID with find:

=MID($A2,FIND("?.???",$A2),5)

- But the ? wildcards doesn't seem to be working.

Is there any wildcards which will help me to pick out the 1.001 pattern from a text? (These can be anything like 4.112, 8.006 etc...)
Maybe a formula that can handle regex?

Thanks

Thomas

 

[Skovgaard]

Hi guys.

I have a database, with text strings in Column A.

Here is a 4 row sample of the data:

1.001 v08 - Able to develop Quality Manual in compliance to ISO and Corporate Requirements

1.001 v08 - Aware about Quality Management Manual

Ismeri a molding termék kiengedésének menetét - SUC 1.134 v02 TPL3.2 v01

Ismeri a raktári lerakodás és bevételezés teljes folyamatát (SOP 1.032 v03)

These are text strings where I need the number from the text #.### and the version number

I was trying to extract the numbers and then the version numbers after them but I was unable to find a formula that will handle wildcards.

I tried MID with find:

=MID($A2,FIND("?.???",$A2),5)

- But the ? wildcards doesn't seem to be working.

Is there any wildcards which will help me to pick out the 1.001 pattern from a text? (These can be anything like 4.112, 8.006 etc...)
Maybe a formula that can handle regex?

Thanks

Thomas

Is it always the first number in the string?
If so, you can use below formula

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),9)

/Skovgaard

 

[szita2000]

Is it always the first number in the string?
If so, you can use below formula

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),9)

View attachment 75491

/Skovgaard

Oooh. That's clever!
It is working for the best part of my dataset.
Thanks!

 

[Peter_SSs]

Based on your sample data, this might also be a possibility.

22 10 05.xlsm
	A	B
1
2	1.001 v08 - Able to develop Quality Manual in compliance to ISO and Corporate Requirements	1.001 v08
3	1.001 v08 - Aware about Quality Management Manual	1.001 v08
4	Ismeri a molding termék kiengedésének menetét - SUC 1.134 v02 TPL3.2 v01	1.134 v02
5	Ismeri a raktári lerakodás és bevételezés teljes folyamatát (SOP 1.032 v03)	1.032 v03
szita2000

Cell Formulas
Range	Formula
B2:B5	B2	=MID(A2,FIND(".",A2)-1,9)

 

[szita2000]

Based on your sample data, this might also be a possibility.

22 10 05.xlsm
	A	B
1
2	1.001 v08 - Able to develop Quality Manual in compliance to ISO and Corporate Requirements	1.001 v08
3	1.001 v08 - Aware about Quality Management Manual	1.001 v08
4	Ismeri a molding termék kiengedésének menetét - SUC 1.134 v02 TPL3.2 v01	1.134 v02
5	Ismeri a raktári lerakodás és bevételezés teljes folyamatát (SOP 1.032 v03)	1.032 v03
szita2000

Cell Formulas
Range	Formula
B2:B5	B2	=MID(A2,FIND(".",A2)-1,9)

Thanks Peter.

 

[Peter_SSs]

Thanks Peter.

You're welcome.