How to return the string between 5th and 6th Spaces in a String

0 votes

What I have?

A column of strings [Description] 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.

Sample substring to be 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.

What I did?

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

But that does not work.

Thank you in advance.

Jun 27, 2018 in Tableau by Atul
• 10,230 points
347 views

1 answer to this question.

0 votes

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 MIDis 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)
)
answered Jun 27, 2018 by sdfg
• 5,550 points

Related Questions In Tableau

0 votes
1 answer

how to get the count of non zero values in a row

In first part you should solve the ...READ MORE

answered Apr 4, 2018 in Tableau by Atul
• 10,230 points
2,106 views
0 votes
1 answer

How to count occurrence of value and percentage of a subset in tableau public?

Although it sounds like a fairly easy ...READ MORE

answered Jun 5, 2018 in Tableau by Atul
• 10,230 points
10,300 views
0 votes
1 answer

How to automatically redirect and open a URL action in dashboard?

Hi, You can use url to provide search ...READ MORE

answered Mar 12, 2019 in Tableau by Cherukuri
• 33,010 points
921 views
0 votes
1 answer

How to add a story with same sheet with different parameters and filters added in tableau?

Hi Riya, Follow these steps to add sheets/dashboard ...READ MORE

answered Mar 22, 2019 in Tableau by Cherukuri
• 33,010 points
1,926 views
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,230 points
8,146 views
0 votes
1 answer

Calculated filed with if-then

Below is the required code:  IF (NOT ISNULL([test2])) ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,230 points
833 views
+2 votes
1 answer
0 votes
1 answer

Reset filters in Tableau

I understood your question (I hope so!). ...READ MORE

answered Mar 30, 2018 in Tableau by QueenBee
• 1,820 points
3,152 views
0 votes
1 answer

How to calculate the percent of records within a group in tableau?

 You can click the measure SUM(Number of Records) and ...READ MORE

answered May 9, 2018 in Tableau by sdfg
• 5,550 points
3,059 views
+1 vote
1 answer
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP