Excel Tip: Converting numbers to text - retrospectively

No matter how much we think we know Excel, we still come across new things all of the time.

This is one a client showed me the other day - and it's a neat little trick!

How many times have you had a column of codes that Excel is reading as numbers and you need to be read as text (to work with a VLOOKUP or SUMIF for example)?

You can change the number format to text but this only partly solves the problem. It means any future numbers entered will be seen as text, but Excel only reads the existing codes as text once you have hit F2 and return on each one. I've found myself rattling down many a column like this in the past.

This little tip, however, means that I never have to do that again!

The approach involves using the Text to Columns feature that is designed for breaking text out into columns. The final step of the wizard, however, allows you to specify the format of each resulting column. This is how this tip works.

Simply follow the following steps:

• Highlight the column of numbers;
• Click Data, Text-to-Columns;
• Select Delimited and click Next;
• And Next again;
• Select Text as the column data format;
• Click Finish.

That's it - no more F2, Return, F2, Return, F2, Return, F2, Return, F2, Return.......

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".