Extract name out of string

twanqlik — Fri, 22 Dec 2017 12:42:01 GMT

I'm trying to extract a ticket ID out of a string.

Example strings:

CL AR 20171211 PHSCRC CA ADHOC Conversion Time Off - Diciembre 2017 #NMQ-644-92867

TicketID =#NMQ-644-92867

CL AR 20171211 PHSCRC CA ADHOC Conversion Time Off - Diciembre 2017 NMQ-644-92867

TicketID = NMQ-644-92867

CL RU 20161017 CRBCRC CA CLCP Welcome LGJ-309-18486

TicketID = LGJ-309-18486

CL BG 20161121 CRBCRC CA CLCP Cross sell Black Friday Campaign

TicketID = (BLANK)

If there is a ticketID it always placed at the end of the string and always consist out of:

xxx-xxx-xxxxx

#xxx-xxx-xxxxx

Currently it almost works with the following function:

Trim(Mid(Name, Index(Name, '-')-4)) as Ticket,

It almost works, if the '-' is also used before the ticketID it basically fails:

CL AR 20171211 PHSCRC CA ADHOC Conversion Time Off - Diciembre 2017 #NMQ-644-92867

Result TicketID = Off - Diciembre 2017 #NMQ-644-92867

While it should be: #NMQ-644-92867

Anyone who has an idea for this?

Re: Extract name out of string

sunny_talwar — Fri, 22 Dec 2017 12:51:47 GMT

Try this

Trim(Mid(Name, Index(Name, '-', -2)-4))

pradosh_thakur — Fri, 22 Dec 2017 12:55:55 GMT

this should work

Trim(Mid(Name, Index(Name, '-', -2)-4))

twanqlik — Fri, 22 Dec 2017 13:00:13 GMT

Awesome, thanks!