How can we create a headline title that is in the Center Across Selection mode?
Merge Cells vs Center across selection
In Excel, we have two solutions. We can select sales and use the Megre&Center command from the Home tab (Fig. 1)
Fig. 1 Megre&Center command
Excel will merge cells into one cell and align it in the center (Fig. 2).
Fig. 2 One, centered cell
This solution, however, has got a small drawback as it created one cell instead of many cells. When we try to select only one column and go a bit too high, we end up in selecting every column (Fig. 3)
Fig. 3 All columns are selected
But, when we go down again, we will select only one column again, so it’s not a big problem. Sometimes, we want to move our data a bit to the right or left. When we try to copy our range, we can’t select only cell B1, which is a bit problematic. After selecting the whole range, we want to move it to the right. However, there’s still a problem. If we want to move the range only a bit to the left or right, we cannot do it (Fig. 4).
Fig. 4 Merged cells cannot be moved only a bit
We have to go far enough to the right to make Excel copy the merged cell (Fig. 5).
Fig. 5 Merged cells moved
I want to show you one more solution. Let’s deselect the Megre&Center command. Now that we have our range selected, we can press the Ctrl + 1 shortcut to go to the Format Cells window. In the window, we have to go to the Alignment tab, then select theCenter Across Selection command in the Horizontal area, and press OK (Fig. 6).
Fig. 6 Format Cells window
We can see that our selected cells have merged. But this time, when we try to select one whole column, we can do it (Fig. 7).
Fig. 7 Whole column selected
You have to remember that now the value is located in cell A1. There’s nothing in B1, C1 and D1 cells. The value was just centered across selection. Let’s now change the coloring of the headline title and try to move it a bit to the right (Fig. 8).
Fig. 8 Moving the range
As we can see, it’s possible (Fig. 9).
Fig. 9 The whole range moved a bit
When we have the cell in the Center Across Selection mode, it’s also important that when we want to add some text to one of the cells, the Center Across Selection option will work only on the cells that are not filled (Fig. 10)
Fig. 10 Center Across Selection working only in cells A1, B1 and C1
Sometimes, our data is combined into one string and we want to extract only a piece of information, which will help us properly understand our data.
How to extract a date number and income from text string with a fixed width
Let’s start with extracting the date. What is important in our example, is that each string of information is of the same length, i.e. it has got a fixed width.
Fig. 1 Data of the same string lengths
We can clearly see, that the date starts with the eighth sign and has got eight signs itself (mm/dd/yy). If we want to extract a certain piece of information, we have to use the MID function. First, we have to write A2 in the argument, because our information is located in this cell. Then, we write 8 because we start with the eighth sign, and then 8 again because we want to extract eight chars. And we have our formula (Fig. 2).
=MID(A2,8,8)
Fig. 2 Formula for extracting a date
After copying it down, we have the results. It’s important though, that the MID function, just like all text functions, returns results as text. We can see it because each date is aligned to the left. It may be problematic if we want to do some date operations. We can change the text by adding 0 to our formula. In other words, we can perform any mathematical operation that won’t change the returned date (Fig. 3).
=MID(A2,8,8)+0
Fig. 3 Adding 0 to dates
Now, we can see that the values are aligned to the right. It means that Excel treats them like numbers, i.e. like dates. We can extract Id numbers the same way. They start with the first sign and have got 6 digits in total. We can also use the MID function. First, we select cell A2, then write 1 because it starts from the first sign, then we write 6 because we extract 6 digits (Fig. 4).
=MID(A2,1,6)
Fig. 4 MID function formula for extracting Id numbers
As we can see, Excel extracted Id numbers. Since it is text, Excel left the leading zero for us. We can leave Id numbers as text if it’s important for us. If it isn’t, we can do the same as with dates, i.e. add zero (Fig. 5).
=MID(A2,1,6)+0
Fig. 5 Adding 0 to Id numbers
We can see that Id numbers moved to the right and the leading zero vanished. The last piece of information we want to extract is the Income. Income values don’t have a fixed width. But, since it’s the last piece of information, we can extract more signs than we need. Let’s extract also the dollar sign. The dollar sign is in the seventeenth position. We start writing the MID formula, then we add cell A2, then 17, and then we have to add the number of places we want too extract. Let’s write 10. It’s, in fact, more that we need, however it’s not less either. The MID function will extract the information to the end without any errors (Fig. 6).
=MID(A2,17,10)
Fig. 6 MID function for extracting Income
As in our previous formulas, the results are also text. By adding zero, we change the results into numbers.
MID(A2,17,10)+0
Fig. 7 Text into numbers
We can see that now the results are numbers. However, we can see that we lost the dollar sign. This is the way Excel converts numbers with dollar signs before them. Indeed, the dollar sign isn’t important in our data, so we should start from the eighteenth sign (Fig. 8).
Fig. 8 Removing the dollar sign
Now, we have only numbers. If we want to add a currency, we just go to the Home tab, drop down theGeneral bar and select the Currency option (Fig. 9).
Fig. 9 Adding a currency
When we have our results with zeros at the end, we can remove them by pressing the Decrease Decimal option (Fig. 10).
