How to quickly convert a whole column of numerics/non-numerics to text in Excel

Image by Pexels from Pixabay

What I love about my job is that, nearly every day, I have one of those "I didn't know that!" moments - for example, where I find out there is a quick way to do something that was otherwise quite tedious and potentially error prone.

I have regularly come across the problem in Excel where a column includes values that are generally numeric, but can also sometimes include an alphabetic character. Excel will often assume that the numeric value is number, when I really need the whole column to be treated as text. 

When this happens, it can cause problems for any formulae that try matching against the column values - if the formulae expects text, but the value stored is numeric. Changing the overall column format to text is not enough - the numeric values already in the column remain as numbers even when this is done.

In preparing one of the recent Boot Camp sessions for Microsoft Excel, I discovered that that there is such an easy way to convert a column like that so that all entries are text - with no need for formulae or copying and pasting values (which is what I used to do!)

Members of the iTandCoffee Club can access this week's handy hint, which looks at how to use an option in the Data tab of Excel to quickly and easily format an entire column as text.

Format an Excel column as text

---

Many more great tips like this for Excel users

This is just one of the many great tips covered in the Boot Camp for Excel class series, video series and book.

​We will run the class series again soon (see below for any scheduled dates) - but those of you interested in learning more in your own time can access all these great tips any time by purchasing access to the videos an/or purchasing the book that accompanies the video series.

​Here are the details.

Microsoft Excel Bootcamp - Access to 8-Part Video Series (120 days)

A$200.00

Purchase 120 days' access to watch all 9 parts the Microsoft Excel Boot Camp series of videos. These 9 parts are being added each week over the period of 8/8/20 to 10/10/20 (excluding 28/8/20). (Note that the access period will become 90 days for purchases made after 10/10/20.)

Here's what's covered in each part:

Week 1 – Getting Started

In this first session of our Microsoft Excel Boot Camp, we start at the very beginning – creating a new Excel workbook and understanding Excel terminology – Workbook, Worksheet, Cell, Rows, Columns, Formulae. We take a guided tour of the main areas of the Excel app (the Ribbons); we look how to easily ‘zoom ‘your document to a size that suits your eyes; saving your document; re-opening your Excel file; and understanding where OneDrive fits in to the picture (and whether to save your Excel files there). We also look at the Templates that you can use to get started on your spreadsheet.

Week 2 – Creating a customer list with some simple formatting and formulae

In this second session, we use the example of a customer list, looking at: entering some simple data into your list; re-sizing columns and rows (manually and using ‘autofit’); text wrapping, entering different types of data into cells (eg text, integers, numbers, dates, dollars); how to select one or more cells (or whole rows / columns); formatting cells (size, bold, colour, background colour, number format, date format); selecting rows, columns and cells; inserting and deleting columns and rows. We then look at some basic formulae for adding the content of rows and columns, and how to copy formulae and formatting across the multiple cells.

Week 3 – Creating a standard invoice with calculations for GST and totals 

In Part 3, we look at the example of a standard invoice: how to format this invoice as an A4 sized page to include a business logo and business details; how to set up your invoice for entry of the items you wish to charge for; setting rows and columns to automatically calculate GST and totals; hiding the grid lines in printed version; including borders around required areas; turning the invoice into a Template that you can use over and over and how to access that template when you need it; setting up your worksheet so that it fits to a single page width; converting that invoice into a PDF that you can send via email and/or save somewhere on your computer/device.

Week 4 – Sorting, filtering, totals/sub-totals and finding/removing duplicates

Part 4 looks at working with data that needs sorting, sub-totalling, grouping or that has duplicates that need to be removed. We also look at the wonderful ‘filter’ options available, to allow you to more easily find the data you are looking for in what may be a very long list of rows – based on whatever criteria you specify.

Week 5 – Basic & Useful formulae PLUS data validation 

In part 5, we focus in this part on using Formulae in your workbook – looking at some of the more common formulae that relate to data in a single worksheet, and across multiple worksheets. We also look at how to set up data validations, which can help with data entry and consistency – setting up a ‘pick list’ of values and how this can ensure entered data is limited to this list.

Week 6 – Importing/exporting CSV files, working with sheets, conditional formatting

In Part 6, we look at importing a ‘comma-delimited’ file (a file in text format, with commas – or some other ‘delimiter’ separating the fields) into Excel format, and at how to create a CSV file from a sheet in your Excel workbook. We look at working with multiple sheets – adding, copying, renaming, moving, creating another workbook from a sheet, hiding, and protecting sheets. We also look at setting up conditional formatting that will automatically bold, colour, underline (or any other format) cells, columns and rows in your sheet.

Week 7 – Summarising and Reporting your data - Pivot Tables and Charts

Part 7 looks at how utilise the Pivot Table feature to see your data represented in a summarised format, counts, totals, averages and other functions reflected in this summarised format. We also look at presenting your data in charts that provide a wonderful visual representation of what is otherwise a flood of text and numbers!

Week 8 – All sorts of handy tips, shortcuts and formulae

In Part 8, we look at really handy shortcuts, features, formulae and tips – to help you use Excel efficiently and effectively. This session will capitalise and expand on what we have covered in earlier sessions, quick ways of achieving things that require several keystrokes and use of the mouse abd discovering great formulae that you may not realised are available.

Shop

On Sale

On Sale

Excel Boot Camp (Microsoft 365) - Compilation of Parts 1-8 (Paperback)

A$75.00 A$60.00

This 159-page paperback guide has been published as a companion to the Microsoft 365 Boot Camp for Excel video series from iTandCoffee, so is designed to be used in conjunction with that series. 

It has been produced based on the content of the slideshow from that video series, so the style is ‘bullet points’ rather than detailed descriptions. Refer to the corresponding video for the more detailed descriptions of each of the areas covered in this document.

It reflects the descriptions and screenshots as shown in parts 1-8 of the video series - see details of contents of each part here. To purchase access to watch the videos, visit this page.

Price shown is for local pickup from Camberwell. (Contactless pickup can be arranged - call 03 9889 9468 to arrange a time.)

Delivery within Australia also available - cost for this item is $15 for Parcel Post, $18 for Express Post.

Note. Higher cost may apply if you also purchase other items as part of the same delivery.)

We use Australia Post for delivery, so any delays applicable at the time you order are out of our control.

Shop

On Sale

On Sale

Excel Boot Camp (Microsoft 365) - Compilation of Parts 1-8 (PDF)

A$55.00 A$40.00

This 159-page PDF guide is a companion to the Microsoft 365 Boot Camp for Excel video series from iTandCoffee, so is designed to be used in conjunction with that series. 

It has been produced based on the content of the slideshow from that video series, so the style is ‘bullet points’ rather than detailed descriptions. Refer to the corresponding video for the more detailed descriptions of each of the areas covered in this document.

It reflects the descriptions and screenshots as shown in parts 1-8 of the video series - see details of contents of each part here. To purchase access to watch the videos, visit this page.

Important information before you purchase (please read): 

After purchase of this product, you will be provided a link to download the purchased PDF file to your device - this link will be valid for 3 days, to allow you to complete the download to your device. We recommend opening the downloaded book in the Books app if you have an iPad.

If you are not familiar with the process of downloading files in Safari on the iPad/iPhone (as this has changed since iOS 13), here is a web page that you should read before purchasing.

The email you receive confirming your purchase will include a link to these instructions at the bottom - in case you need to refer to them again.

On your computer or other device, please look for the downloaded PDF file in your standard Downloads location.

Shop

---

Scheduled Excel Boot Camp classes

If no classes are currently shown below, you can register your interest in future classes here.