extract a string from variable text

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"

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

Originally Posted by Phuocam

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

In FILTERXML() terms...

Formula:

=TRANSPOSE(FILTERXML("<F><d>"&SUBSTITUTE(SUBSTITUTE(A2,"_"," ")," ","</d><d>")&"</d></F>","//d[translate(.,'1234567890','')!=. and .*0!=0 and string-length()>3]"))

Not perfect yet.

My final master piece FILTERXML()

Formula:

=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]")

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

Formula:

=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]"),")","")

Originally Posted by DJunqueira

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

Formula:

=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]"),")","")

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

Originally Posted by esaji

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 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 !!!!!

@DJunqueira just one string is still showing.data

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

For the very particular case

Formula:

=IF(ISNUMBER(RIGHT(T1001.Data,1)),T1001.Data,LEFT(T1001.Data,5))

Originally Posted by DJunqueira

For the very particular case

Formula:

=IF(ISNUMBER(RIGHT(T1001.Data,1)),T1001.Data,LEFT(T1001.Data,5))

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

=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]"),")",""),IF(ISNUMBER(RIGHT(T1001.Data,1)),T1001.Data,LEFT(T1001.Data,5))

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

Tried my UDF? Getting rid of .data as well

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)