Article 18 – LTRIM and RTRIM through Excel Formulas

So, we have TRIM function in Excel. Almost all programming languages provide LTRIM and RTRIM functions also but Excel doesn't provide LTRIM and RTRIM. The same is provided in VBA but most of the Excel users are not using VBA. They are simple folks who want to accomplish their day to day job through Excel functions only.

Before I delve into LTRIM and RTRIM, let's recap TRIM.

TRIM, basically, removes all spaces from your string and if there are more than one space between your words, it will convert them into only one space. So, it removes all leading spaces, all trailing spaces and all spaces between words except one. If there is only one space between words, it will leave that space untouched.

Below are the examples of operating with TRIM function –

"Vijay  A   Verma" = "Vijay A Verma" (two spaces between Vijay and A have been reduced to one. Three spaces between A and Verma have been reduced to one)

"  Vijay      Verma" = "Vijay Verma"(Two spaces before Vijay have been reduced to no space and Six spaces between Vijay and Verma have been reduced to one)

"Vijay      Verma   " = "Vijay Verma"(Three spaces after Vijay have been reduced to no space and Six spaces between Vijay and Verma have been reduced to one)

"    Vijay   Verma        " = "Vijay Verma" (Four spaces before Vijay have been reduced to no space, 3 spaces between Vijay and Verma have been reduced to one space and Eight spaces after Verma has been reduced to no space)

So, I am sure that now you have got what does TRIM do.

Now, I talked about that most programming languages including provide LTRIM and RTRIM function. So, what do LTRIM and RTRIM do –

1. LTRIM removes all leading spaces and leaves all other spaces untouched. Hence, if I operate  LTRIM on following –

"    Little   Mary   " = "Little   Mary   "  (All four leading spaces have been trimmed so that is no space before first word. It has left spaces between two words and spaces after last word untouched)

2. RTRIM removes all trailing spaces and leaves all other spaces untouched. Hence, if I operate  LTRIM on following –

"    Little   Mary   " = "    Little   Mary"  (All three trailing spaces have been trimmed so that is no space after last word. It has left spaces between two words and spaces before first word untouched)

Now, let's get back to making LTRIM and RTRIM through Excel functions-

The formula for LTRIM

=REPLACE(A1,1,FIND(LEFT(TRIM(A1),1),A1)-1,"")

So, this particular Excel formula would act like LTRIM that is it would remove all leading spaces and would leave all other spaces untouched.

The formula for RTRIM

=IFERROR(REPLACE(A1,MAX(INDEX((MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1)
<>" ")*ROW(A1:INDEX(A:A,LEN(A1))),,))+1,LEN(A1),""),"")

Above function would act like RTRIM that is it would remove all trailing spaces and would leave all other spaces untouched.