Trim, Concatenate, Remove Punctuation, Left and Right (MS-Excel)

In today’s entry we will explore, or in the case of concatenate, re-explore some of the more useful text modification functions within MS-Excel.

The example work sheet which we will be utilizing is illustrated below:

This worksheet can be found within this website’s GitHub Repository.

Let’s say that you wanted to create a single cell within the work sheet which contained the following formatted text:

The large cat, sat his large rear on, the tiny mat.

Typing this out ourselves, or manually formatting the text contained within each cell, seems like the direct way of completing this task. However, we will assume that achieving such is impossible in our example scenario.

TRIM()

This function, according to the Microsoft Office website:

“Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.”

Let’s apply this function to each cell entry from columns A to D.

This is established by entering “=TRIM()” within each destination cell, with the function being initiated to target each corresponding cell.



The result is as follows:

CONCAT()

Now that the previous step has been completed, we can begin the concatenation process. Within a destination cell, (we will use E4), we will type the following code:

=CONCAT(A2, " ", B2, ", ", C2, " ",D2)

Illustrated, this appears as such:

The result being:

We’ve almost completed our task. All that remains is a single modification. We must remove the “;”, at the end of the sentence, and in its place, insert a “.”.

(NOTE: “CONCAT” replaces the “CONCATENATE” function which existed within the older versions of Excel. If the “CONCAT” function is not performing its task, try utilizing the “CONCATENATE” function in the same manner as illustrated above.)

LEFT() and RIGHT()

Though these functions are not immediately useful as they pertain to the completion of our task, they should nevertheless be discussed.

LEFT() and RIGHT() are two separate MS-Excel functions. Each function provides a similar task, that task being, the return of a specified number of characters from a previously indicated cell. 

RIGHT() and LEFT() dictate the direction of the character count.

RIGHT() – Selects characters from left to right.

LEFT() – Selects characters from right to left.

So, if for example we typed:

=LEFT(A2, 3)

into an empty cell, and A2 contained:

The large cat,

The value within the destination cell would now contain:

The

Likewise, if we were to type:

=RIGHT(A2,4)

into an empty cell, and A2 contained:

The large cat,

The value within the destination cell would now contain:

cat,

RemovePuncuation()

Another useful function, which is unrelated to this exercise, is RemovePuncuation(). As the name indicates, RemovePuncuation() creates a cell entry which contains the contents of an indicated cell, with all punctuation removed.

Therefore, if we typed:

=RemovePuncuation(A2)

into an empty cell, and A2 contained:

The large cat,

The value within the destination cell would now contain:

The large cat

This function removes ALL punctuation. Therefore, all periods, commas, apostrophes, semi-colons, etc., would be removed from the text within the destination cell.

Removing the Final Cell Character

We will finish our exercise by creating a new cell entry which contains the contents our original cell, with the exception of the final character (;).

This can be achieved with the code below:

=LEFT(E2, LEN(E2)-(RIGHT(E2) = ";"))

Implemented, this resembles the following:

The result being:

The initial function is specifying the removal of the semi-colon.

The subsequent function is adding a period in lieu of the removed semi-colon.

E2 is the cell value which is being targeted. This target value can be modified based on what the situation entails. The value at the end of the function “;”, can be modified to whatever the final character  is (“.”, “,”, etc.) within the target cell which requires removal.