topic Extracting a number from a text field in Discussions

Extracting a number from a text field

robressler — Sat, 10 Jun 2023 23:25:05 GMT

I have a column containing text notes entered by technicians. They enter the system usage hours, which I need to extract into it's own column. Fortunately, the data is pretty consistent, and so I have been able to pull the info out using Regex Match(), but selecting the last value is tripping me up.

Regex Match( :diagnosis, "(ys.+sage: ?)(\d{1,5})\n" );

How do I extract the System Usage hours from the text block effectively?

Sample text looks like this:

UDI# 123456789123456789123456789456123
Device received, missing cover.
Major Reason For Service: xxxxxxxxxx
System usage: 2372
System PM due in: 7627
xxx usage: 120
yyy usage: 208

Re: Extracting a number from a text field

jthi — Wed, 20 Jan 2021 21:31:06 GMT

If the data is consistent enough you could do this without regex by using Substr with Contains (and Length)

Names Default To Here(1);
text ="UDI# 123456789123456789123456789456123

    Device received, missing cover.

    Major Reason For Service: xxxxxxxxxx

    System usage: 2372
    System PM due in: 7627
    xxx usage: 120
    yyy usage: 208";
  
stringToSearch = "System usage: ";
sysUsage = num(Substr(text, Contains(text, stringToSearch) + length(stringToSearch), 4));
show(sysUsage);

Edit1: Or by using Words and Word:

Names Default To Here(1);
text ="
UDI# 123456789123456789123456789456123

Device received, missing cover.

Major Reason For Service: xxxxxxxxxx

System usage: 2372
System PM due in: 7627
xxx usage: 120
yyy usage: 208";

list_of_rows = Words(text, "\!N");
Show(list_of_rows);
sysUsage = Word(3, list_of_rows[4]);
Show(sysUsage);

Edit2: And finally regex with same text:

Names Default To Here(1);
text ="
UDI# 123456789123456789123456789456123

Device received, missing cover.

Major Reason For Service: xxxxxxxxxx

System usage: 2372
System PM due in: 7627
xxx usage: 120
yyy usage: 208";

//note "\2"
matchRegex = Regex(text, "(System usage: )(\d{1,5})", "\2");
Show(matchRegex);

//note index of number
matchList = Regex Match(text,"(System usage: )(\d{1,5})");
Show(matchList);
Show(matchList[3]);

Re: Extracting a number from a text field

txnelson — Wed, 20 Jan 2021 21:29:44 GMT

Here is a solution that uses JSL functions to extract the System Usage

Num( Word( 1, Substr( :Diagnosis, Contains( :Diagnosis, "System usage:" ) + 14 ), "\!n" ) )

I have attached the above data table