LEFT, RIGHT FUNCTION

[DataAnalyst]

Hello, I have 2 columns with qtrs formatted as Q2/15 and so on.

My calculation for deliquent quarters will look at both these quarter fields. Since i cant subract in this format, I used the LEFT function to extract the year values from both the fields which works fine.

However, when i use the right function to get say the value 2 in Q2/2015, i get all 0s. Is there a regular expression i can use or try something else. I just need number 2 from Q2/2015.  I have tried trim function as well.

Thanks!

[BenMoss]

Could you please post your desired output and data and perhaps we could offer more hands on help then :)

[MarqueeCrew · Accepted solution]

Substring([field],1,1)

That will get you '2'


If you want the number 2,

ToNumber(Substring([field],1,1))

[steffimerlin]

Hi,

I would like to get some more clarity on how the RIGHT and LEFT function works in Alteryx since I don't have the software to try it out myself.

if for example consider the below phrase -

Water and Ice

Could someone please explain to me what would the output be for RIGHT([string],7) and LEFT([string],7) :

Does the RIGHT and LEFT function count spaces and punctuation as well? And is it 0 based? 

Thanks,
Steffi

[ChrisTX]

See this documentation page for all functions: https://help.alteryx.com/current/Reference/Functions.htm

[neilgallen]

To specifically answer the question, 

RIght would output "and Ice"

while

Left would output "Water a"

it does count spaces and punctuation, and lastly, directly from the documentation:

"About Length and Strings: When referencing the LENGTH of a string the first character is counted as 1 (the length of the following string "record" is 6). HOWEVER, when referencing the character position within a STRING, positions are actually counted BETWEEN characters. So think of the first character's position as 0.  Therefore, the position of the letter "c" in the string "record" is at position 2."

[steffimerlin]

Thank you so much. That really helped! 

[TS4]

Thanks for mentioning that the count starts at 0.  First time I used substring, and I could not figure out what was going on!

[Idyllic_Data_Geek]

I will like to retain the right 5 digits from a number. For example , from 0123456789..I will like to keep 56789

[Luke_C]

@Idyllic_Data_Geek, if it's stored as a numeric field, you'd have to do Right(ToString([FIELD]),5)