Extract Specific Text From Cell

[emacleod]

Hello and thank you for your time to assist me -

I need a formula that can evaluate the text based values in a cell to specifically return any text string with numbers and their decimals which maybe contained within. Essentially, I need to extract application versions in an installation directory path. Installations adhere to the following conventions using decimals to denote the octates, like:

12.34.56.7890
1.2.3.4
001.223.445.9876
9.8.008.425

Cell Value titled Installation Path would contain a text string like:
\endor4381\bkup\generic12.34.56.7890rpr\application\remote\4l16ogpackage\9114topdraw46\concise

In the above example, the formula would only return the '12.34.56.7890' since this is meets the criteria of an expected application version. Other numbers present in the install path would be ignored since they do not meet.

My data is in an Excel Table. I am currently running Excel 365 MS Build 2208

Let me know if there is any additional information you may need?

Once again, THANK YOU!

emacleod

 

[emacleod]

A huge "THANK YOU!" to all who responded back in such a timely fashion with multiple solutions. After some testing, all three solutions did work. It seems the VB solution offered by Peter_SSs is the more efficient with 100 more records being returned out of a dataset of ~32k.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, if you wanted to try an alternative formula approach, with no helper columns required, you could test this. It should extract any sequence of digits that contains at least one "."
If it is possible to have two (or more) such sequences in the same cell then the formula will extract each one to a separate cell - see row 10 below.
If such data as row 10 can exist and you wanted to stay with a UDF approach then the UDF could be modified to 'extract all' as well. Post back if that is wanted.

emacleod.xlsm
	A	B	C
1	Data	Version
2	G:\endor4381\bkup\generic12.34.56.7890rpr\application\remote\4l16ogpackage\9114topdraw46\concise	12.34.56.7890
3	U:\cornerthough\callctr\1.2.3.4	1.2.3.4
4	/raptor/businessforms34-bf/blue001.223.445.9876-autolog\stream	001.223.445.9876
5	//slice//cmr9.8.008.425/dir	9.8.008.425
6	/aeu/businessinteligence/forecast-lite/7.6.9a1	7.6.9
7
8	No version 32 here
9	/cramfin/totalbkp/app/view/archive/ver12.1.2.4.20130719/calc72-937thr%	12.1.2.4.20130719
10	/cramfin/totalbkp/app/view/archive/ver12.1.2.4.20130719/calc72-9.37thr%	12.1.2.4.20130719	9.37
Sheet2

Cell Formulas
Range	Formula
B2:B9,B10:C10	B2	=LET(t,A2&" 1",c,MID(t,SEQUENCE(LEN(t)),1),s,TEXTSPLIT(TRIM(CONCAT(IF(ISNUMBER(c+0)+(c="."),c," ")))," "),FILTER(s,ISNUMBER(FIND(".",s)),""))

 

[emacleod]

Thank you for the additional formula approach using "LET." I'll keep note this, if needed in the future. The VB supplied is extremely efficient in finding single application versions and suffices for the current need.

 

[Peter_SSs]

OK, cheers.