extract a string from variable text

[esaji]

Dear Experts

I would like to define a excel IS number (SEARCH) formaula to help me fetch a specified string from the variable alerts like this one

T1133
T1071.001

From variable alerts rows like some time alerts are with _ some time the string start after a SPACE and some its under Brackets ()

mitre_T1071.001 Communication with known Bad ip's
mitre T1110.003 Multiple Users Attempting To Authenticate Using Explicit Credentials
mitre_t1033_1 System User Discovery Net Commands/Alert
Network Sniffing 2 (MITRE 1040) /Alert

can you create a Search Is number formula that extract the T1133 as single number as well with dots from variable alerts name ?

e.g. a formula that search MITRE with _ with () and with dots and copy the adjacent value in the next columns as T1133.001 or T1133
=IF(ISNUMBER(SEARCH("MITRE"

[Shareez Saleem]

Please Login or Register  to view this content.

[Pete_UK]

You can put this in C2 (say), and then copy down:

=IF(ISNUMBER(SEARCH("Mitre",A2)),LEFT(MID(SUBSTITUTE(A2,")"," "),SEARCH("Mitre",A2)+6,FIND(" ",SUBSTITUTE(A2,")"," "),SEARCH("Mitre",A2)+6)-6)&" ",FIND(" ",MID(SUBSTITUTE(A2,")"," "),SEARCH("Mitre",A2)+6,FIND(" ",SUBSTITUTE(A2,")"," "),SEARCH("Mitre",A2)+6)-6)&" ")-1),"")

You may have to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

It gets all the numbers, but some have some other text as there are other symbols in the data ("_" and "-", and so on).

Hope this helps.

Pete

[Phuocam]

Or try this in C2:

=IFERROR(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,1,SEARCH("Mitre",A2)+5,""),")"," "),"_"," ")," ",REPT(" ",100)),100)),"")

[DJunqueira]

In FILTERXML() terms...

Please Login or Register  to view this content.

Not perfect yet.

[JohnTopley]

UDF

Please Login or Register  to view this content.

Assumes required field starts with"T1" or "1"

[Bo_Ry]

Please try

=MID(LEFT(A2,MIN(FIND({" ",")","_"},A2&")_ ",SEARCH("mitre",A2)+6))-1),SEARCH("mitre",A2)+6,20)

[DJunqueira]

My final master piece FILTERXML()

Please Login or Register  to view this content.

[DJunqueira]

My final master piece (bullet proof correction) FILTERXML()

Please Login or Register  to view this content.

[JEC.]

How about this Regex

Please Login or Register  to view this content.

[esaji]

Or try this in C2:

=IFERROR(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,1,SEARCH("Mitre",A2)+5,""),")"," "),"_"," ")," ",REPT(" ",100)),100)),"")

can you try to add few more conditions for these three strings

MITRE_T1102.002-Proxy outgoing persistent traffic direct to external ip result T1102.002-Proxy
MITRE-T1090.002-Long Established Connection Over Proxy result= T1090.002-Long
MITRE-T1001.Data Obfuscation result =T1001.Data

I wanted to see them without .data -Proxy and -Long

thanks u

[esaji]

My final master piece (bullet proof correction) FILTERXML()

Please Login or Register  to view this content.

This is working perfectly , can you please share the logic of each check I wanted to understand how this formula is checking multiple conditions one by one with Translate and NOT starts conditions

[esaji]

@DJunqueira just one string is still showing.data

MITRE-T1001.Data Obfuscation T1001.Data
is it possible to remove .data from here ?

[DJunqueira]

This is working perfectly, can you please share the logic of each check I wanted to understand how this formula is checking multiple conditions one by one with Translate and NOT starts conditions

Trying to explain:
The formula has two main parts, the SUBSTITUTE() part inside FILTERXML() and the part at the end of this function, after //d (green part).

=SUBSTITUTE(FILTERXML("<F><d>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_"," "),"-"," "),")",") ")," ","</d><d>")&"</d></F>","//d[translate(.,'1234567890','')!=. and string-length()>3 and not(starts-with(., 'P'))][1]"),")","")

The blue SUBSTITUTE() part I substituted "_" ; "-" for space " " and ")" for ") " because otherwise FILTERXML function would transform some strings into numbers (ie: Spearphishing Attachment (MITRE 1566.001) / Alert) were 1566.001 would loose the point separator and also other numbers would return as numbers, what wasn't desired. The first SUBSTITUTE(), takes care of the ")" character outside of FILTERXML() funcion.

[/COLOR]SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_"," "),"-"," "),")",") ")

In the second part translate(.,'1234567890','')!=. did most of the work, separating strings that had numbers, but were not numbers, from the rest. string-length()>3 and not(starts-with(., 'P'))][1] took care of some particular cases, some texts that were small and had numbers like P2P; 8; F5; v2, and a word Part2 was also showing up. From that point I had 7zip spilling out and I took care with [1].

[translate(.,'1234567890','')!=. and string-length()>3 and not(starts-with(., 'P'))][1]"

[DJunqueira]

For the very particular case

Please Login or Register  to view this content.

[JEC.]

Tried my UDF? Getting rid of .data as well

[esaji]

@DJunqueira thank you so much and would love to subscribe you if you are teaching excel on youtube - I really appriciate for saving my life and this task and the time you spend in beautifully explaining the formula hats off

thank you so much !!!!!

[esaji]

I have not tried it ,will it convert the sheet macro enabled excel file ? a function i have to use with that file ?

[esaji]

For the very particular case

Please Login or Register  to view this content.

is this will part of first formula plus second as i cant see the selected Row like A2 ? currently excel is throwing error

Please Login or Register  to view this content.

[JEC.]

Open Vba, add a module and paste the UDF
Then call the function in Excel like a normal function.

Give it a try!

=jec(A1)

[DJunqueira]

@DJunqueira thank you so much and would love to subscribe you if you are teaching excel on youtube - I really appriciate for saving my life and this task and the time you spend in beautifully explaining the formula hats off
thank you so much !!!!!

You are welcome and tank you for your kind words.

[DJunqueira]

is this will part of first formula plus second as i cant see the selected Row like A2 ? currently excel is throwing error

Big formula now:

Please Login or Register  to view this content.

Could be much smaller with the LET() function.

[esaji]

Big formula now:

Please Login or Register  to view this content.

Could be much smaller with the LET() function.

perfect and thank you much for valuable efforts and time to make this wonderful end result what i was looking for