Issues with Substring search

[Krish_Penubarthi]

Hi.

I need to search for a particular keyword in my string and extract data accordingly. The string is ' ' (space).

Example : If the name column has Robert it should give me Rob and if it has got Dave Paul, i should return DPau. 

I am not able to get the solution for this. 

Also, I need to skip the spaces if any at last as well.

Thanks!!

[afv2688]

Can you explain yourself better? Why DPau? Do you have some kind of list for the abbreviation?

[Krish_Penubarthi]

My scenario is that, if the column has single character word, it should extract first 3 characters. If it is a 2 character word, I should display 1st letter of 1st word and 1st 3 of second word.

[OllieClarke · Accepted solution]

The following formula uses the CONTAINS() function to search for a space and parses the names according with what you wanted.

IF !CONTAINS(TRIM([Field1]),' ') THEN Left(TRIM([Field1]),3)
ELSE REGEX_Replace(TRIM([Field1]),'(\w).*?\s(\w{3}).*','$1$2')
ENDIF

As it uses regex, there are some in built assumptions - namely that the field will only ever have 1 space in it, between the first and second word. The TRIM() function removes any trailing or leading white space

[Krish_Penubarthi]

Thanks a lot. It worked perfectly.