Forum Discussion
Solved
Extracting data out of a String
Hi All, I'm after a simpler way of extracting data out of a string: My dataset has a column that looks like this for example (1,000's of rows) Expense 15AUG23 63.74L DSL FORD PICKUP ...
=IFS(ISNUMBER(SEARCH("DSL",A2)),"DSL",ISNUMBER(SEARCH("UNL",A2)),"UNL",TRUE,"") and =TAKE(TEXTSPLIT(TEXTBEFORE(A2,{" DSL";" UNL"})," "),1,-1) or =IFS(ISNUMBER(SEARCH("DSL",A2)),"DSL",ISNUMBER(SEARCH("UNL",A2)),"UNL",TRUE,"") and =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TAKE(TEXTSPLIT(TEXTBEFORE(A2,{" DSL";" UNL"})," "),1,-1),"GAL",""),"L",""),",",".")) and =IFS(ISNUMBER(SEARCH("GAL",TAKE(TEXTSPLIT(TEXTBEFORE(A2,{" DSL";" UNL"})," "),1,-1))),"GAL",ISNUMBER(SEARCH("L",TAKE(TEXTSPLIT(TEXTBEFORE(A2,{" DSL";" UNL"})," "),1,-1))),"L",TRUE,"")
=IFS(ISNUMBER(SEARCH("DSL",A2)),"DSL",ISNUMBER(SEARCH("UNL",A2)),"UNL",TRUE,"")
and
=TAKE(TEXTSPLIT(TEXTBEFORE(A2,{" DSL";" UNL"})," "),1,-1)
or
=IFS(ISNUMBER(SEARCH("DSL",A2)),"DSL",ISNUMBER(SEARCH("UNL",A2)),"UNL",TRUE,"")
and
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TAKE(TEXTSPLIT(TEXTBEFORE(A2,{" DSL";" UNL"})," "),1,-1),"GAL",""),"L",""),",","."))
and
=IFS(ISNUMBER(SEARCH("GAL",TAKE(TEXTSPLIT(TEXTBEFORE(A2,{" DSL";" UNL"})," "),1,-1))),"GAL",ISNUMBER(SEARCH("L",TAKE(TEXTSPLIT(TEXTBEFORE(A2,{" DSL";" UNL"})," "),1,-1))),"L",TRUE,"")
O wow, thank you very much for this! What software did you write that in please?
- Do I just put that into the formula bar?