Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Last instance of a character

pcatterson
11 - Bolide

How do I identify the location of the LAST instance of particular character (base zero)?

 

C:\\Location\Folder\Folder\Files.xlsx|Sheet1  has its last "\" at character 26

C:\\Location\File.txt   has its last "o" at character 10

C:\\Location\Folder\Folder\Folder\Folder\FileswithSheets.xlsx|Sheet1  has its last "|" at character 61

 

I assume I need some regex, but I'm not very confident using regex yet.

6 REPLIES 6
MarqueeCrew
20 - Arcturus
20 - Arcturus
If you use the REVERSESTRING() function it will be easy to find. You can then reverse it back after you are done.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
pcatterson
11 - Bolide

Would you have an equally quick way of finding the second to last instance of a character in a string, etc?

MarqueeCrew
20 - Arcturus
20 - Arcturus
Yes. After your first replacement, do it again before you reverse it takes normal.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
jack_morgan
9 - Comet

Hey @pcatterson,

So there are A LOT of different ways to do this in Alteryx. Revstring is one way but you can also do a combo left and right calc on your field. See the attached app. To run it, just double click it. To see how its complied, right click and edit it. From there you can see how the function works. The only reason that I added the app piece is so that you could see how you could make it easier to update than updating the "10" in the formula, which looks like this in the workflow:

 

right(left([File Path],10+1),1)

 

This is essentially taking the left 10 of your field, then the last character of that string that is returned. The "+1" gives you that zero base. If you wanted character was 16, update the 10 to 16.

 

Hope that helps.

JohnJPS
15 - Aurora

One thing to be careful about is, if the string you're looking for is more than 1 character in length, to reverse that as well... here's a generic solution looking for the last instance of [stringToFind] inside the parent [testString] ...

IIF(FindString([testString], [stringToFind]) > -1,
    Length([testString]) - FindString(ReverseString([testString]),
                                      ReverseString([stringToFind])), -1)
SubratDas5
10 - Fireball

Thanks for this approach, @MarqueeCrew . 

 

This helped me with a critical XML parse logic.

Labels