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

excel

Formulas and Functions

dscheikey

Jun 18, 2024

JamesDLG

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

JamesDLG.xlsx11 KB

JamesDLG

Copper Contributor

Jun 18, 2024

O wow, thank you very much for this! What software did you write that in please?

dscheikey

Bronze Contributor

Jun 18, 2024

JamesDLG

I don't understand the question.

EXCEL

BrainSoft

JamesDLG
Copper Contributor
Jun 18, 2024
Do I just put that into the formula bar?
- dscheikey
  Bronze Contributor
  Jun 18, 2024
  JamesDLG
  
  Take a look at the example file. All the formulae can be found there. If you have a different language environment, these will be reformatted and rewritten for you.
  If you do not use the "." for decimal places, you must change the formula.
  - JamesDLG
    Copper Contributor
    Jun 18, 2024
    Ok perfect, thanks so much for your help!

Forum Discussion

Extracting data out of a String

Resources