Cut out cerain area form the rows of a column. Which tool?

[Marek89]

hey guys,

just one important question: as you can see on the screenshot, i have a column with segments, which always start with "Custom Segment >". This is the part, which i actually do not need. Now i want to learn different oppurtunities to solve this problem. So Maybe a new colum what includes just the part i need

- how Can i delete the part "Custom Segment >"?

- how Can i just select everything what starts with "IQDX..."

Thank you so much! :)

[jnans · Accepted solution]

@Marek89  For this you can use the Text to Column tool. This can be found in the parsing tool pallet. Choose the > as your delimiter and then 2 columns, rename what you have to and then use a select tool to get rid of the unwanted columns. You might also have to use a cleanse tool to get rid of the unwanted space of your new column as well.

[danilang · Accepted solution]

Hi @Marek89 

If you just want to delete the "Custom Segment >"  you can use a formula tool with 

Replace([FullPath],"Custom Segment >","")

If you want to select everything from IQDX onwards, use

Substring([FullPath],Findstring([FullPath],"IQDX"))

If you want the make the changes directly in the FullPath column, specify "FullPath" as the OutputColumn.  If you want the result to appear in a new column set the Output Column to the name of the new column

Dan

Dan

[Marek89]

thank you all, everything works :)

Can you just shortly explain to me what this formula does: Substring([FullPath],Findstring([FullPath],"IQDX"))

Is it looking for a specific expression like "IQDX" in this case and takes everything what follows after that?

Thank you so much! :)

[jaimonsk]

yes.

The normal syntax for substring function is  is Substring( [Col Name],start,end)

Find string function gets the position where the string "IQDX" starts. So basically what Substring([FullPath],Findstring([FullPath],"IQDX")) would be doing is, getting the position where "IQDX" starts and then extract the rest data

[Marek89]

hey, thank you.

but what does "start" and "end" in this context specially mean, do you have an example?

because in this case Substring([FullPath],Findstring([FullPath],"IQDX")) -> "Findstring([Fullpath)] would be "start" and IQDX would mean "end" which does not make sense to me.

Thank you very much

[danilang · Accepted solution]

Hi @Marek89 

This is a example of a nested function. 

In general any time you have a function of the form 

Func(param1,param2,...)

You can replace any of the params with another function as long the second function returns a type that is compatible with replaced param.  If you replace a string param, the replacing function has to return a string, a number param can be replaced with a function that returns a number

As @jaimonsk pointed out, the syntax for substring is 

Substring( [Col Name],start,end)

What he didn't mention is that "end" is a optional parameter.  You can omit the "end" parameter and the substring function will return the string from the "start" position to the end of the line.  That's the case with your function

Substring([FullPath],Findstring([FullPath],"IQDX"))

There is no "end" parameter in this statement

The inner most part in green replaces the start parameter and is executed first and returns the start position of "IQDX" in the string [FullPath].  Lets say that this position is 12.  Once this done, the outer function executes replacing the Findstring([FullPath],"IQDX") with 12, so the expression becomes 

Substring([FullPath],12),  

returning the part of [Fullpath] starting at position 12.

Dan

[Marek89]

Hey, great answer! Thank you! :)