Left Function

[Lynnise]

Is there a fucntion or formula that I can use that will mimic the the LEFT function in Excel? I am trying to pull the first 5 charactors from a string ?

Thank you in advance!

[MarqueeCrew · Accepted solution]

LEFT([STRING],5)

[Lynnise]

Thank you so much and "DUH" to myself! I did have brackets intially.

[Suzanne]

Mark, what if you had this:

"Trying to find the first position of o"

In excel, you can search the string and get back that the first "o" is at position 9 (might be 8.  I don't remember if search is 0 based).

I have a situation in Alteryx where I'm trying to find a character "|".  I'd like to do something like  --> right([fieldname],length([FieldName])-search([fieldname],'|'))

[cmcclellan]

Use FINDSTRING the same way as you would in Excel :) 

[Suzanne]

Hi cmcclellan.  FINDSTRING brought back True/FAlse, not the position of the character in the string.  Unless I'm not using it correctly ... a real possibility -:)

[MarqueeCrew]

@Suzanne,

I'm sorry but I'd have to go to a library (inside of a museum) to dust off a book on Excel to answer your question.  I'll answer it in the 21st century with a regular expression.  I still don't know what your objective is and why you wouldn't use a text to columns to parse on the | symbol.  But before I go there, here is a cut & paste from the help on FindString():

FindString(String,Target): Searches for the occurrence of a particular string (Target) within another string (String) and returns the numeric position of its occurrence in the string.

Returns the 0 based index of the first occurrence of Target in String. Returns -1 if no occurrence.

Example

FindString([Name], "John") returns 0 when the string contains John and returns -1 when the string does not.

IF (FINDSTRING([Name], "John") =0) THEN "John Smith" ELSE "Other" ENDIF returns John Smith when the string contains John and returns Other when the string does not.

Regex_Replace([Field], "(.*)|(.*)",'$2')

That expression will group everything to the left of a Pipe ''|" as Group 1 or $1.  It also groups everything after the pipe as $2.  The replacement that I chose was the second group.  So if you are creating a new variable as what is after the pipe, it will work for you.  If you wanted everything up to the first pipe (not including it), then change $2 to $1.

Cheers,

Mark

[cmcclellan]

Not true/false, you just have to understand what the results are saying ( @MarqueeCrew already explained), but this might help more :

It's a zero-based calculation, so you might need to add 1 to the result depending on what you want to do ... 

0 = John found at the start

1 = John found.  First character is counted as 0, second character is 1

7 = John found, calculation as per above

-1 = John not found at all

FINDSTRING IS case-sensitive = JOHN, John and john will give 3 different results

[kishore_pvkk]

I am using the below function but I am getting error

Left([GL Account Number], 1)

[kishore_pvkk]

Below is the error