Return a string between the 5th and 6th spaces in a string
I have a row column that looks like this:
Target host: dcmxxxxxxc032.erc.nam.fm.com Target name: dxxxxxxgsc047.erc.nam.fm.com File system / u 01 has 4.98% free space - dropped below warning (20) or critical (5) threshold.
Column name: [Description]
The substring I would like to return is (dxxxxxxgsc047.erc.nam.fm.com)
The only consistency in this data is that the search string occurs between the 5th and 6th occurrences of spaces "in the string, and after the phrase" Target Name: "The length of the substring changes, but it always ends with a different one, so my attempt to capture a substring between the 5th and 6th spaces.
I tried
MID([Description],((FIND([Description],"Target Name: "))+13),FIND([Description]," ",((FIND([Description],"Target Name"))+14)))
But it doesn't work.
(Edit: we are using Tableau 8.2, Tableau 9 features cannot be part of the solution, thanks!)
Thank you in advance for your help.
source to share
In Table 9, you can use regular expressions in formulas, this makes things easier:
REGEXP_EXTRACT([Description], "Target Name: (.*?) ")
Alternatively, in Table 9, you can use the new FINDNTH function:
MID(
[Description],
FINDNTH([Description]," ", 5) + 1,
FINDNTH([Description]," ", 6) - FINDNTH([Description]," ", 5) - 1
)
Until table 9, you will have to use string manipulation techniques similar to what you tried, you just need to be very careful with arithmetic and provide the correct arguments (the third argument in MID
is the length, not the index of the ending character, so we need to subtract the index of the starting character ):
MID(
[Description]
, FIND([Description], "Target Name:") + 13
, FIND([Description], " ", FIND([Description], "Target Name:") + 15)
- (FIND([Description], "Target Name:") + 13)
)
source to share
Well, you need to find "Target name:" and then "" after it, not that hard. I will split into 3 fields to be more clear (you can mix everything in one field). BTW, you were in the right direction, but the last field on MID () should be the length of the string, not the char position
[to begin]:
FIND([Description],"Target name: ")+13
[end]:
FIND([Description]," ",[start])
And finally what you need:
MID([Description],[start]+1,[end]-[start]-1)
This must be done. If you want to use the 5th and 6th "approaches", I would recommend that you find each of the "" before the 6th.
[the first]:
FIND([Description], " ")
[second]:
FIND([Description], " ",[1st] + 1)
Etc. Then:
MID([Description],[5th]+1,[6th]-[5th]-1)
source to share