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

 

[Sufiyan97]

Just one example is not enough, please provide 5-6 rows with different possible text lines.

 

[emacleod]

My apologies for not supplying the appropriate amount of examples. Below is a screenshot of a few. I'm unable to upload a Mini-sheet.

Here is the paste values data, too:

Installation Path Extracted Version
G:\endor4381\bkup\generic12.34.56.7890rpr\application\remote\4l16ogpackage\9114topdraw46\concise 12.34.56.7890
U:\cornerthough\callctr\1.2.3.4 1.2.3.4
/raptor/businessforms34-bf/blue001.223.445.9876-autolog\stream 001.223.445.9876
//slice//cmr9.8.008.425/dir 9.8.008.425
/aeu/businessinteligence/forecast-lite/7.6.9a1 7.6.9
/cramfin/totalbkp/app/view/archive/ver12.1.2.4.20130719/calc72-937thr% 12.1.2.4.20130719


--Thank You

 

[kweaver]

How would an Excel routine KNOW which set(s) of numbers in any given string would be the one you want?
There needs to be some criteria to determine this.

 

[emacleod]

Excel would not know. Ideally, there would be a lookup table which compare the possible results in the table. There are too many variables to account for in order for me to accomplish that. This is why I'm taking this approach, to determine if there is a application version candidate in the installation path, based on that ###.####.####.###### format. That numbering condition is the criteria. Again, a lot of variables to account for in order to carve-out examples like I had included above.

 

[Sufiyan97]

I am agree with @kweaver
There should be some logic in each line to extract desired result.

 

[emacleod]

I understand - the number and decimal format is all I have to go on. I appreciate your time. You don't have to go any further with this inquiry. It can be closed.

Thank you

 

[kweaver]

Never one to give up...how about the following that uses some helper columns (final extraction is in E):

021623 Misc.xlsx
	A	B	C	D	E
1
2	G:\endor4381\bkup\generic12.34.56.7890rpr\application\remote\4l16ogpackage\9114topdraw46\concise	34.56.7890	G:\endor4381\bkup\generic12	12	12.34.56.7890
3	U:\cornerthough\callctr\1.2.3.4	2.3.4	U:\cornerthough\callctr\1	1	1.2.3.4
4	/raptor/businessforms34-bf/blue001.223.445.9876-autolog\stream	223.445.9876-	/raptor/businessforms34-bf/blue001	001	001.223.445.9876
5	//slice//cmr9.8.008.425/dir	8.008.425/	//slice//cmr9	9	9.8.008.425
6	/aeu/businessinteligence/forecast-lite/7.6.9a1	6.9	/aeu/businessinteligence/forecast-lite/7	7	7.6.9
7	/cramfin/totalbkp/app/view/archive/ver12.1.2.4.20130719/calc72-937thr%	1.2.4.20130719/	/cramfin/totalbkp/app/view/archive/ver12	12	12.1.2.4.20130719
Sheet10

Cell Formulas
Range	Formula
B2:B7	B2	=LEFT(TEXTAFTER(A2,".")&"a",MIN(IFERROR(SEARCH(CHAR(SEQUENCE(26,,65,1)),TEXTAFTER(A2,".")&"a"),""))-1)
C2:C7	C2	=TEXTBEFORE(A2,".")
D2:D7	D2	=RIGHT(C2, LEN(C2) - MAX(IF(ISNUMBER(MID(C2, ROW(INDIRECT("1:"&LEN(C2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(C2))), 0)))
E2:E7	E2	=D2&"."&SUBSTITUTE(SUBSTITUTE(B2,"-",""),"/","")

 

[GuzmanServices]

Assuming you can utilize Helper columns, I came up with the following solution to this problem.
Note there needs to be at least 3 periods and the digits cannot exceed 3 digits - similar to an IP address.
- The formula in Cell B2 is =IF(IFERROR(VALUE(MID($A2,SEARCH(".",$A2)-3,3)),0)<>0,MID($A2,SEARCH(".",$A2)-3,3),IF(IFERROR(VALUE(MID($A2,SEARCH(".",$A2)-2,2)),0)<>0,MID($A2,SEARCH(".",$A2)-2,2),MID($A2,SEARCH(".",$A2)-1,1)))
- The formula in Cell C2 is =IF(IFERROR(SEARCH(".",(MID($A2,SEARCH(".",$A2)+1,3))),0)=0,MID($A2,SEARCH(".",$A2)+1,3),IF(IFERROR(SEARCH(".",(MID($A2,SEARCH(".",$A2)+1,2))),0)=0,MID($A2,SEARCH(".",$A2)+1,2),MID($A2,SEARCH(".",$A2)+1,1)))
- The formula in Cell D2 is =IF(IFERROR(SEARCH(".",(MID($A2,SEARCH(".",$A2)+2+LEN(C2),3))),0)=0,MID($A2,SEARCH(".",$A2)+2+LEN(C2),3),IF(IFERROR(SEARCH(".",(MID($A2,SEARCH(".",$A2)+2+LEN(C2),2))),0)=0,MID($A2,SEARCH(".",$A2)+2+LEN(C2),2),MID($A2,SEARCH(".",$A2)+LEN($B2)+1+LEN($C2),1)))
- The formula in Cell E2 is =IF(IFERROR(SEARCH(".",(MID($A2,SEARCH(".",$A2)+3+LEN(C2)+LEN(D2),3))),0)=0,MID($A2,SEARCH(".",$A2)+3+LEN(C2)+LEN(D2),3),IF(IFERROR(SEARCH(".",(MID($A2,SEARCH(".",$A2)+3+LEN(C2)+LEN(D2),2))),0)=0,MID($A2,SEARCH(".",$A2)+3+LEN(C2)+LEN(D2),2),MID($A2,SEARCH(".",$A2)+LEN($B2)+1+LEN($C2),1)))
- The formula in Cell F2 is =$B2&"."&$C2&"."&$D2&"."&$E2

All formulas should work as needed when copied!

 

[Peter_SSs]

You could try a user-defined function like this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function GetVersion(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+\.)+\d+"
    If .Test(s) Then GetVersion = .Execute(s)(0)
  End With
End Function

emacleod.xlsm
	A	B
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 here
9	/cramfin/totalbkp/app/view/archive/ver12.1.2.4.20130719/calc72-937thr%	12.1.2.4.20130719
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=GetVersion(A2)