Search Not Just Numbers

Tuesday 25 March 2014

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.......

Excel Expert Course

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".

2 comments:

  1. I have never had to F2 all the way down (wee,wee,wee,....)
    First I am not sure why you would need to do the conversion for Vlookup.
    Second you could array enter =VLOOKUP(""&A1,""&E2:F6,2,0) which would convert your search item and lookup table to text without actually changing your data.
    Third it is not good practice to change raw data in any way. If you do text to columns your destination should not overlap your source.
    Regards
    Brian

    ReplyDelete
    Replies
    1. Brian

      I would agree wholeheartedly wiith your point about not editing raw data. This was what the client was using it for and I recommended that the spreasdheet should be altered to expect the data in this format rather than change it each time. The times I am talking about are where you have data that was originally entered with no validation or structure and you may be mapping to raw data pulled from a system. This is a quick solution to getting the existing data into the new format (to match the format of the raw data that you are referencing.

      I like your array formula though. I can see that being handy where I can't guarantee that the two data tables can match going forward.

      Delete