Skip to main content
Question

Extract e-mail address from ticket description


Forum|alt.badge.img+2

HI, i’m trying to extract an email address using expression builder using a formula i’ve found on this forum (This post)

 

substring('{{ticket.description_text | sanitize_html}}',indexOf('{{ticket.description_text | sanitize_html}}', 'Work email:',0)+length('Work email:'), indexOf('{{ticket.description_text | sanitize_html}}', Status', indexOf('{{ticket.description_text | sanitize_html}}', 'Requestors:',0)) )

 

I get error “missing )”

 

The closest I got was 

 

  {{ticket.description_text}},

    indexOf({{ticket.description_text}}, 'Work email:', 0) + length('Work email:'),

    length({{ticket.description_text}}) - (indexOf({{ticket.description_text}}, 'Work email:', 0) + length('Work email:'))

)

 

produced john@doe.c where the email address in full was john@doe.com 

 

can anyone help please I feel I am so close 😅

Did this topic help you find an answer to your question?

6 replies

DanielRuff
Skilled Expert
Forum|alt.badge.img+7
  • Skilled Expert
  • 72 replies
  • December 3, 2024

Your first expression is fine. Just add another single quote, it should be ‘Status’.

 

Works in my expression builder with the correct single quotes.

 


Forum|alt.badge.img+2
  • Author
  • Top Contributor
  • 5 replies
  • December 3, 2024
DanielRuff wrote:

Your first expression is fine. Just add another single quote, it should be ‘Status’.

 

Works in my expression builder with the correct single quotes.

 

thank you for taking a look, I hugely appreciate it.  With the single quote added, now I get “Invalid Syntax - Third operand's value must be > second operand's value.”


DanielRuff
Skilled Expert
Forum|alt.badge.img+7
  • Skilled Expert
  • 72 replies
  • December 3, 2024

Please provide a screenshot and the code. 

I don’t understand your last line. Try the following. As this should be easier as third operand in your substring:

length(‘{{ticket.decription_text | sanitize_html}}’)


Forum|alt.badge.img+2
  • Author
  • Top Contributor
  • 5 replies
  • December 3, 2024
DanielRuff wrote:

Please provide a screenshot and the code. 

I don’t understand your last line. Try the following. As this should be easier as third operand in your substring:

length(‘{{ticket.decription_text | sanitize_html}}’)

 

code from above:

 

substring('{{ticket.description_text | sanitize_html}}',indexOf('{{ticket.description_text | sanitize_html}}', 'Work email:',0)+length('Work email:'), indexOf('{{ticket.description_text | sanitize_html}}', 'Status', indexOf('{{ticket.description_text | sanitize_html}}', 'Work email:',0)) )

 


DanielRuff
Skilled Expert
Forum|alt.badge.img+7
  • Skilled Expert
  • 72 replies
  • December 4, 2024

The expression works as expected. But if it cannot find “Status” the indeOf returns -1 and the last operand cannot be smaller as otherwise the expression(substring) is being created from character 3 to -1 which is not possible.

Using this string for the placeholder returns the same error as you said:
“adadwad Work email:john@doe.com Sta”



When trying this placeholder I get the correct substring:
“adadwad Work email:john@doe.com Status”

 


Forum|alt.badge.img+2
  • Author
  • Top Contributor
  • 5 replies
  • December 4, 2024

thank you very much, it worked!! I think i was getting myself very confused with the Status thing, there is no such thing I wanted to look for, so i adapted it for what will be shown in a real ticket


Reply