Why can’t Excel be more like Word? – Change Case

Sep 10, 2018 | Excel Hints and Tips

Why is there no Change Case icon in Excel?

Change case is there in Word and PowerPoint but why not in Excel I hear you cry!  Well, it just isn’t!!  It would be a really useful option to have but as it isn’t there we need to accept it and find another way around the problem!  There are a couple of solutions.  Both involve creating a new column and then the result can be copied and then pasted on top of the original data using paste values.  One of the methods can be used going back many versions while the other is a brand new feature in Excel 2016.

Method 1: Using Functions

 

The functions of upper, lower and proper change the text into upper case, lower case and title case respectively.

1. Go to the next blank column (or insert a blank column next to the text)

The structure of all 3 functions is the same so let’s illustrate with upper

2. The structure of the function is:

=UPPER(text)

For example =UPPER(A2) if we want to put the data in A2 into upper case

Change Case

3. Autofill the result by dragging down using the cursor shape of a black cross that appears when you hover over the bottom right corner of the cell containing the calculation.  This only needs to be performed if  there is a column of data, not just an individual cell.

4. If required we can replace the original data with the new, uppercase version by following the steps below.

  • Select the data created by the calculations
  • Copy
  • Select the first cell that had the original data
  • From the Paste drop-down select Paste Values

The function column can now be removed if required.

Method 2: Using Flash Fill

Flash fill has only been available since Excel 2016 but if you have it then you will find it amazing.  It looks for patterns in the original data to guess what you are trying to do, and you normally only need to enter one record for this to work.

1. Go to the next blank column (or insert a blank column next to the text).

2. Type the first word as you want to see it e.g JOE BLOGGS.

3. In the next cell down type in the first letter of the next word in the case you want it to be and Excel will hopefully suggest what you want in the rest of the column.

This doesn’t always work!!  If it doesn’t suggest anything then try the following instead.

1. Type the first new record as before.

2. Go to the next cell below.

3. Choose Flash fill from the Data Tab to force it to find a pattern.

Flash fill can actually be used for any sequence of data where there is a pattern.  If Excel can find it then it will Flash Fill it!

Want to learn more about how Flash Fill and Functions can help you in your business then email me at lara@laramellortraining.co.uk to arrange a time to discuss your requirements.