MS-Excel How-To: Manipulate string in a Worksheet.

MS-Excel How-to: Manipulate text in Worksheet

The Question
I was recently asked a question if it was possible to write a formula to put a space between the second word in each cell in the worksheet. If you look at the example above, the 1st column is the source. The 2nd column is the output by applying the formula. The arrow indicates the space inserted in the word.

The Solution

MS-Excel Formula, an example

The Explanation

IF Function in MS-Excel

The IF function requires 3 parameters:

• The first parameter is the logical test.
• The second parameter is the output if the logical test result is true.
• The third parameter is the output if the logical test result is false.

In the formula example shown above, you will notice there are nested IF functions. In the example, the IF function evaluates the logical test to see if the text length in the cell is equal to 19. If the return values is true, it extracts 16 characters of the text from the left (using the LEFT() function), concatenate with a space (” “) and finally concatenate the string with 3 characters of the text from the right (using the RIGHT() function).

If the return value is false, a nested IF function is evaluated to check if the length of the text is equal to 18. If the return value for the second IF function is true, it will output the first 16 characters using the LEFT() function. Concatenates the string with a space (” “) and further concatenates two characters from the right using the RIGHT() function.

Finally if the return value of the second nested IF function is false, it simply returns the text value from the reference cell (in this example it’s A2).

This formula was then copied to all 20,000 rows in the worksheet voila job done in minutes. If you have a better approach using a different formula in MS-Excel, please feel free to share your example(s).