2nd Occurrence of a character in a string

Question

I am new to Alteryx,

I am having below data of Names, I want to segregate Names column into "First name", Middle name", "Last Name".

As per my logic, I am trying to use LEFT() and RIGHT() functions and wanted to find out second position/occurrence of  character space, Can anyone let me know, how to find second occurrence of character in a string?

Names

Nilesh Dilip Haval
Akshay Dilip Haval
S P Balasubramaniyam
P K Jain
Naval Kumar Singh
Rahul Anand Patel
Viyalakshmi Raman Mahadevkar
Lakshmianand George Fernandes

RolandSchubert · Accepted Answer

Hi @Vaish369 ,

you can find the 2nd occurence of a character using the formula

FindString(SubString([Names], FindString([Names], ' ') + 1), ' ')

The "inner" FindString finds the first occurence, using Substring you "cut off" the first part of the string starting at the first character after the space, the "outer" FindString find the second space. You can calculate the starting position of the last name as:

Character position of 1st space + 1 ( = first character after first space) + Character position of space in the remaining part + 1 (= first character after first space): 
FindString([Names], ' ') + 1 + FindString(SubString([Names], FindString([Names], ' ') + 1), ' ') + 1

Btw: If you want to split the name in First Name, Middle Initial and Last Name, you could also eg. replace the spaces by "|"  and use a Text To Columns tool.

Let me know if it works for you.

Best,

Roland