Returning the string between the 5th and 6th Spaces in a String

Question

I have a column of strings that look like this:

Target Host: dcmxxxxxxc032.erc.nam.fm.com Target Name: dxxxxxxgsc047.erc.nam.fm.com Filesystem /u01 has 4.98% available space - fallen below warning (20) or critical (5) threshold.

The column name is [Description]

The substring I would like returned is (dxxxxxxgsc047.erc.nam.fm.com)

The only consistency in this data is that the desired string occurs between the 5th and 6th occurrences of spaces " " in the string, and after the phrase "Target Name: " The length of the substring varies, but it always ends in another " ", hence my attempt to grab the substring between the 5th and 6th spaces.

I have tried

MID([Description],((FIND([Description],"Target Name: "))+13),FIND([Description]," ",((FIND([Description],"Target Name"))+14)))

But that does not work.

(Edit: We use Tableau 8.2, the Tableau 9 only functions can't be part of the solution, thanks though!)

Thank you in advance for your help.

Answer 1

In Tableau 9 you can use regular expressions in formulas, it makes the task simpler:

REGEXP_EXTRACT([Description], "Target Name: (.*?) ")

Alternatively in Tableau 9 you can use the new FINDNTH function:

MID(
     [Description],
     FINDNTH([Description]," ", 5) + 1, 
     FINDNTH([Description]," ", 6) - FINDNTH([Description]," ", 5) - 1
   )

Prior to Tableau 9 you'd have to use string manipulation methods similar to what you've tried, just need to be very careful with arithmetic and providing the right arguments (the third argument in MID is length, not index of the end character, so we need to subtract the index of the start character):

MID(
   [Description]
   , FIND([Description], "Target Name:") + 13
   , FIND([Description], " ", FIND([Description], "Target Name:") + 15)
     - (FIND([Description], "Target Name:") + 13)
)