Trim

Values sometimes have spaces at the start or end because of mistakes in data entry or the use of a split operation to break apart a CSV (comma separated list). These aren't always easily visible but can cause problems if you're trying to do exact matches for a Lookup/Join. Trim allows you to strip out trailing or leading characters (usually white space by default) to clean up your text field.

Excel

=~TRIM!~(‹string›)

=TRIM(A1) // " Recruiter " -> "Recruiter"

Tableau

Tableau supports three TRIM() operations:

• ~TRIM!~(‹string›) removes leading and trailing spaces

• ~LTRIM!~(‹string›) removes leading spaces only

• ~RTRIM!~(‹string›) removes trailing spaces only

TRIM([Job Role]) // " Recruiter " -> "Recruiter"

LTRIM([Job Role]) // " Recruiter " -> "Recruiter "

RTRIM([Job Role]) // " Recruiter " -> " Recruiter"

Alteryx

Alteryx supports three TRIM() operations:

• ~TRIM!~(‹within_string›«, target_string») removes leading and trailing characters

• ~TRIMLEFT!~(‹within_string›«, target_string») removes leading characters only

• ~TRIMRIGHT!~(‹within_string›«, target_string») removes trailing characters only

TRIM([Job Role]) // " Recruiter " -> "Recruiter"

TRIMLEFT([Job Role]) // " Recruiter " -> "Recruiter "

TRIMRIGHT([Job Role]) // " Recruiter " -> " Recruiter"

NB. If «target_string» is excluded, the default behaviour is to trim whitespace. If it is included, you can specify what character to trim:

TRIM([Job Coded], "*") // "**HR014**" -> "HR014"

OrgVue

Gizmo does not support Trim operations, although OrgVue workspace will usually trim strings by default.

tSQL

• ~LTRIM!~(‹string›) removes leading spaces only

• ~RTRIM!~(‹string›) removes trailing spaces only

• To remove leading and trailing spaces, use ~RTRIM!~(~LTRIM!~(‹string›))

SELECT LTRIM(JobRole) AS JobRole
FROM EmployeesData
-- " Recruiter " -> "Recruiter "

SELECT RTRIM(JobRole) AS JobRole
FROM EmployeesData
-- " Recruiter " -> " Recruiter"

SELECT RTRIM(LTRIM(JobRole)) AS JobRole
FROM EmployeesData
-- " Recruiter " -> "Recruiter"

Python

‹string›.~strip(«target_string»)!~

jobRole.strip() # " Recruiter " -> "Recruiter"
# OR
jobCode.strip('*') # " HR014**" -> " HR014"

NB.

• If «target_string» is excluded, the default behaviour is to trim whitespace.

• If «target_string» is included, you can specify what character to trim.

• The above examples assume variables have been declared for jobRole and jobCode. For more information, go to General > Variables.