How do I delete all content except certain string

[gyagos]

Ive been searching thru the forums and cannot find the answer im looking for. I have a spreadsheet with 2440 rows and im looking to get the following string placed in a different column. Anyhting that starts with cpe:/a:apache:http_server to be exact as there are multiple versions in the spreadsheet and some cels might have 2 entries for 2 different versions
cpe:/a:apache:http_server:2.4.39

The cel in its entirety looks like the following:

"cpe:/a:gnupg:libgcrypt:1.5.3","cpe:/a:ibm:global_security_kit:8.0.50.66","cpe:/a:ibm:tivoli_storage_manager_client:7.1.6.5","cpe:/apenbsdpenssh:7.4","cpe:/a:apache:log4j:1.2.17","cpe:/a:splunk:splunk:8.2.3","cpe:/a:tenable:nessus_agent:10.1.3","cpe:/a:apache:http_server:2.4.6","cpe:/a:apache:log4j:2.17.1","cpe:/a:docker:docker:1.13.1","cpe:/a:fasterxml:jackson-databind:2.12.1","cpe:/a:fasterxml:jackson-databind:2.9.8","cpe:/araclepenjdk:1.8.0.161.14","cpe:/araclepenjdk:1.8.0.312.07","cpe:/aivotal_software:spring_framework:5.0.10","cpe:/a:apache:log4j:2.17.2","cpe:/a:apacheoi:3.17","cpe:/a:apache:struts:1.3.8","cpe:/a:apache:tomcat:8.5.78","cpe:/a:fasterxml:jackson-databind:2.9.7","cpe:/aivotal_software:spring_framework:4.3.14","cpe:/a:jquery:jquery:1.11.3","cpe:/a:nginx:nginx:1.8.0","cpe:/a:gitlab:gitlab:13.11.3","cpe:/a:gnupg:libgcrypt:1.8.6","cpe:/a:nginx:nginx:1.18.0","cpe:/aracle:jre:1.11.0.13.8-lts" Thank you in advance

 

[gyagos]

All those faces look to be when an o follows a colon or when a p follows

 

[Fluff]

What version of Excel are you using & on what platform?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

All those faces look to be when an o follows a colon or when a p follows

In future, if you 'Preview' your post and see those characters, go back out of the preview and surround the text with PLAIN tags like this
[PLAIN] your text here[/PLAIN]

Example
cpe:/a:oracle or cpe:/a:p

More information near the bottom of this page

 

[gyagos]

What version of Excel are you using & on what platform?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Windows running office 365

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	A	B
1
2	"cpe:/a:gnupg:libgcrypt:1.5.3","cpe:/a:ibm:global_security_kit:8.0.50.66","cpe:/a:ibm:tivoli_storage_manager_client:7.1.6.5","cpe:/a:eek:penbsd:eek:penssh:7.4","cpe:/a:apache:log4j:1.2.17","cpe:/a:splunk:splunk:8.2.3","cpe:/a:tenable:nessus_agent:10.1.3","cpe:/a:apache:http_server:2.4.6","cpe:/a:apache:log4j:2.17.1","cpe:/a:docker:docker:1.13.1","cpe:/a:fasterxml:jackson-databind:2.12.1","cpe:/a:fasterxml:jackson-databind:2.9.8","cpe:/a:eek:racle:eek:penjdk:1.8.0.161.14","cpe:/a:eek:racle:eek:penjdk:1.8.0.312.07","cpe:/a:pivotal_software:spring_framework:5.0.10","cpe:/a:apache:log4j:2.17.2","cpe:/a:apache:poi:3.17","cpe:/a:apache:struts:1.3.8","cpe:/a:apache:tomcat:8.5.78","cpe:/a:fasterxml:jackson-databind:2.9.7","cpe:/a:pivotal_software:spring_framework:4.3.14","cpe:/a:jquery:jquery:1.11.3","cpe:/a:nginx:nginx:1.8.0","cpe:/a:gitlab:gitlab:13.11.3","cpe:/a:gnupg:libgcrypt:1.8.6","cpe:/a:nginx:nginx:1.18.0","cpe:/a:eek:racle:jre:1.11.0.13.8-lts"	"cpe:/a:apache:http_server:2.4.6"
Main

Cell Formulas
Range	Formula
B2	B2	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(A2,",","</m><m>")&"</m></k>","//m[contains(.,'cpe:/a:apache:http_server')]"))

 

[Peter_SSs]

Windows running office 365

Please put that information in your Account details as suggested by @Fluff above so that the information is always available to helpers like this.

 

[gyagos]

Thanks for that, how about

Fluff.xlsm
	A	B
1
2	"cpe:/a:gnupg:libgcrypt:1.5.3","cpe:/a:ibm:global_security_kit:8.0.50.66","cpe:/a:ibm:tivoli_storage_manager_client:7.1.6.5","cpe:/a:eek:penbsd:eek:penssh:7.4","cpe:/a:apache:log4j:1.2.17","cpe:/a:splunk:splunk:8.2.3","cpe:/a:tenable:nessus_agent:10.1.3","cpe:/a:apache:http_server:2.4.6","cpe:/a:apache:log4j:2.17.1","cpe:/a:docker:docker:1.13.1","cpe:/a:fasterxml:jackson-databind:2.12.1","cpe:/a:fasterxml:jackson-databind:2.9.8","cpe:/a:eek:racle:eek:penjdk:1.8.0.161.14","cpe:/a:eek:racle:eek:penjdk:1.8.0.312.07","cpe:/a:pivotal_software:spring_framework:5.0.10","cpe:/a:apache:log4j:2.17.2","cpe:/a:apache:poi:3.17","cpe:/a:apache:struts:1.3.8","cpe:/a:apache:tomcat:8.5.78","cpe:/a:fasterxml:jackson-databind:2.9.7","cpe:/a:pivotal_software:spring_framework:4.3.14","cpe:/a:jquery:jquery:1.11.3","cpe:/a:nginx:nginx:1.8.0","cpe:/a:gitlab:gitlab:13.11.3","cpe:/a:gnupg:libgcrypt:1.8.6","cpe:/a:nginx:nginx:1.18.0","cpe:/a:eek:racle:jre:1.11.0.13.8-lts"	"cpe:/a:apache:http_server:2.4.6"
Main

Cell Formulas
Range	Formula
B2	B2	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(A2,",","</m><m>")&"</m></k>","//m[contains(.,'cpe:/a:apache:http_server')]"))

works great if there is only one Apache version. When there are multiple, i receive a #SPILL! in the cel where the formula is

"cpe:/a:apple:xcode:12.4","cpe:/a:vmware:horizon_view_client:8.3.0","cpe:/a:microsoft:office:excel","cpe:/a:microsoft:office:onenote","cpe:/a:microsoft:office:outlook","cpe:/a:microsoft:office:powerpoint","cpe:/a:microsoft:office:word","cpe:/a:apache:http_server:2.4.51","cpe:/a:nginx:nginx:1.7.10","cpe:/a:apache:log4j:2.11.2","cpe:/a:apple:safari:15.4","cpe:/a:tenable:nessus_agent:10.1.3","cpe:/a:zoom:zoom_cloud_meetings:5.9.6_(4993)","cpe:/a:cisco:anyconnect_secure_mobility_client:4.10.05085","cpe:/a:google:chrome:101.0.4951.64","cpe:/a:apache:log4j:2.17.1","cpe:/a:apple:xcode:13.4","cpe:/a:nginx:nginx:1.21.0","cpe:/a:apache:http_server:2.4.53","cpe:/a:google:chrome:102.0.5005.61","cpe:/a:apple:safari:15.5"

 

[gyagos]

works great if there is only one Apache version. When there are multiple, i receive a #SPILL! in the cel where the formula is

"cpe:/a:apple:xcode:12.4","cpe:/a:vmware:horizon_view_client:8.3.0","cpe:/a:microsoft:office:excel","cpe:/a:microsoft:office:onenote","cpe:/a:microsoft:office:outlook","cpe:/a:microsoft:office:powerpoint","cpe:/a:microsoft:office:word","cpe:/a:apache:http_server:2.4.51","cpe:/a:nginx:nginx:1.7.10","cpe:/a:apache:log4j:2.11.2","cpe:/a:apple:safari:15.4","cpe:/a:tenable:nessus_agent:10.1.3","cpe:/a:zoom:zoom_cloud_meetings:5.9.6_(4993)","cpe:/a:cisco:anyconnect_secure_mobility_client:4.10.05085","cpe:/a:google:chrome:101.0.4951.64","cpe:/a:apache:log4j:2.17.1","cpe:/a:apple:xcode:13.4","cpe:/a:nginx:nginx:1.21.0","cpe:/a:apache:http_server:2.4.53","cpe:/a:google:chrome:102.0.5005.61","cpe:/a:apple:safari:15.5"

i figured out that this should be done at the end, or add blank columns in and the data will spill into the black columns

 

[Fluff]

Please don't forget to update your profile as requested.

You need to clear the cells to the right of where you put the formula