Text Examples 4

Text Ex 12

• Real life situation
• When you copy and paste say column A and B into a basic text editor like Notepad, the descriptions and numbers don’t align as shown below (try it)

• We want to create a series of formula that will create a single column with the description and amount included in such a way that they always line up. Once you have that you should be able to copy and paste it into Notepad and see lined up numbers (like this).

Steps

1.     Note cell E5 which specifies that the end result must have a total of 50 cells

2.     In column C, you need to convert the amount in column B into text (use the DOLLAR function)

3.     In column D, determine the number of characters in the description shown in column A

4.     In column E, determine the number of characters in the amount in column C

5.     In column F calculate how many spaces are required (you know how many characters are allowed as per Cell E5, and how many you need for the description and amount for each row)

6.     In column G create a formula that will generate a cell with the correct number of spaces as per column F’s calculation. You will need to search the function wizard (under the TEXT category) for a function that will repeat a blank x number of times.

7.     Join the Description (column A) with the spaces (column G) with the amount (column C) to create a single column with the exact spacing.

If you want to now copy this into Notepad and see if it lines up.

To see the full course contents click here.