MrExcel's Learn Excel #647 - Extending ABC

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Mar 26, 2009.
Excel knows how to extend any series, but it can not extend the letter A to B, C, etc. Episode 647 shows you a couple of tricks to solve this problem.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Let's say that we need to fill in the letters A through Z going down the spreadsheet.
There is no easy way to do this.
Normally if we would type values such as January and grab the fill handle and drag, excel knows how to extend that series.
January February March And of course if we put the number 1 in and Ctrl click and drag the fill handle, it knows how to extend that series 2 3 4 5 6.
Excel knows how to extend all of these series, but it can't extend the letter A.
If I put in the letter A, click the fill handle and drag.
It just fillls in A A A A A all the way down and the fill options box, does not give us an option to extend the series.
So what I find is that there is a secret number code, associated with every letter.
Now I know that A is the number 65 and we could learn that by using =CODE(*A*) It'll tell us that sure enough it's a 65.
So the reverse function is the character function.
If I would ask for =CARE And then I want to put a 65 here, but instead What I'm going to do is 64+ROW(A1).
The row of A1 is a really fancy way of saying the number 1.
Close the parentheses and that will get me an A.
Now when I grab this formula and copy it down to 26 rows.
It will give me the letters A through Z.
Now if this is something you have to do all the time, we might as well import this as a custom list.
We want to convert these formulas to regular text.
So what I do is right click on the right edge, drag right, let go, "Copy here as values only".
Now I have the Letters A through Z selected.
We'll go to "Tools" "Options" On the custom list tab, I'll click "Import" and it imports my list.
Now excel understands.
I can actually type any letter so I can start with C.
Grab the fill handle and drag and excel will extend the series all the way down.
So couple of different ways to fill in the letters.
If it's something that you find that you have to do all the time, setting up a custom list is the easy way to go.
If it's just something you have to do every once in a while, using this formula, the character formula, starting with 65 and copying down is another way to go.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top