Converting Numbers to Text

The difference between numbers and text is a concept that most Excel beginners don’t grasp. It’s not very intuitive. But to work with computers, you must think like a computer. To a computer, numbers are numbers and strings are strings regardless of what they look like.

How about an example. Let’s say you’re preparing data to fill out a form. Oh, I don’t know, just some random form like a Utah Excise Tax Return. You might have data that looks like this:

Experienced Excel users will immediately notice that the 7s are right justified, indicating they are numbers, and the 1F-D is left justified, indicating it’s a string. Everything in the Schedule column should be a string. The key test is: Do I perform mathematical operations on it. If so, it should be a number. If not, then it’s simply a label whose characters happen to be digits instead of letters.

Under most circumstances, this doesn’t really matter. Of course there’s no way I could stand to see two types of data in the same column, but normal people would have no problem with it. And the few that would have a problem would just change the alignment without changing the data type. There are, however, at least two situations where it can cause problems: long numbers and leading zeros. If you enter a 16-digit credit card number into Excel, it will truncate the last digit because Excel can only handle 15 digits of precision. If you enter a number like 04712495135 into a cell, the first zero will be gone as soon as you press enter.

The first thing you should do is to format your Table columns with the proper number format. That is, any columns that should be text should be formatted as text so the new entries are automatically considered text. It’s easy enough to do after the fact. And honestly I’m usually creating a table from data that already exists, so I can’t format a ListColumn before it exists. When you already have numbers in a text column, you need to do one extra step before you format (or after, it doesn’t matter the order).

Select the column and choose Text to Columns from the Data tab.

Choose delimited and click Next. Choose a delimiter that doesn’t exist anywhere in the data. I usually keep Tab checked for two reasons: 1) It’s pretty rare to have tabs in a cell and 2) Tab is the default and I’m speeding through this process pretty quickly (Alt+A, E, Alt+N, Alt+N, Alt+T, Alt+F).

Finally, tell the wizard that the column is Text and click Finish.

If you did it right (and haven’t changed your Excel options to hide certain errors), you’ll see some green triangles next to the “numbers” indicating that Excel is warning you that you have text that looks suspiciously like numbers.

I’ve been working on an excise tax project lately that requires me to use other peoples workbooks and a lot of exported data. I run these steps several times a day so I decided it was time to automate it.

KwikOpen Exclusions

I use a product called SQL Prompt from Redgate in SSMS. Honestly I can’t image life without it. They added a new feature to the grid right-click menu called Open in Excel.

It’s very handy and definitely worth the trip over to my mouse. My only complaint about it is that it adds the resulting workbook to the MRU. That muddies up my KwikOpen Addin (original, update) particularly when I’m searching for “exported” or “results”.

I don’t think they should be adding that to the MRU and I’ve told them so. But in the meantime I’m going to exclude it from my list of files. Here’s the relevant part of the Fill method in the CRcntFiles class.

I really need to build an exclusions interface where I can enter matching strings to exclude rather than putting it in the code. Someday.

AET VBE Tools v1.7

They are finally ready!

This verson includes 3 additional tools, and another option to the existing Cleanup Project Code tool.

Here’s a description of what’s new.

1. Rename Userform Controls
This tool allows you to batch rename all controls on a userform via a simple Find and Replace interface. In addition to changing the control names, it also changes code with the old control name to the new control name.

So, if you want to change all controls that start with “CommandButton” to “btn” or “cb”, etc, this is the way to do it all at the same time!

2. Make Project Variables List
The bigger the project, the more complex it becomes.

This tool analyses your code and makes a list of variables and constants.

Details include:

  • Module Name (Where the variable or constant is)
  • Scope (Public, Private, Type or Procedure)
  • Location (Declaration or procedure name)
  • Name (Variable or constant name)
  • Type (Variable or constant type, eg String, Long, etc)
  • Const (True or null, to discern between variables and constants)
  • Const Value (Value if constant)
  • Found in Project (Count within project)
  • Found in Report (Count within report)
  • Modules Count
  • Procedures Count
  • Unique Values
  • Duplicate Values

    More features are planned in the near future.

    3. Copy Code for Web
    If you need to show your code on the web, normal indenting won’t show. The way to get around it is to convert indent spaces to “ ”.

    This tool does that and copies the code to the clipboard so you can paste it where required.

    4. Delete Orphan Code
    This feature was added at the request of Kellsens Willamos, who has supported me and the development of these tools from the beginning.

    What’s “orphan code”?

    When developing, did you ever click userform controls by mistake? Maybe you get something like this.

    Private Sub lblSelectProject_Click()

    End Sub

    Chances are you don’t want the code, and if there’s nothing between the first and last line, it’s a fair bet that you don’t. The Delete Orphan Code tool looks for code like this and gets rid of it.

    I’ve added it as an option on Cleanup Project Code. To include it, tick the checkbox on the Settings form.

    As mentioned previously, I’ve made AET VBE Tools shareware. But when you see the price, I hope you will agree they are well worth the cost. (And you will get future versions free of charge!)

    You can download them here.

    P.S. I’m looking for affiliates and translators. If interested, email me at aengwirda at gmail dot com.

  • Jumping to a PivotTable’s Source Data

    I use Ctrl+[ all the time to go to the precedents of the ActiveCell. The SourceData for a PivotTable isn’t a precedent, but wouldn’t it be nice if there was a shortcut to go there? Yes, it would.

    Until now, if I wanted to see the SourceData for a PivotTable, I would use Alt, J, T, I, D to activate the Ribbon control for changing the source data (PivotTable Tools – Options – Change Data Source – Change Data Source). That would open the Change PivotTable Data Source dialog. The focus on that dialog is a RefEdit control so Excel navigates to the data. I don’t actually want to change it, though, so I hit ESC and it takes me back to the PivotTable. But I make a note of where it is and I use Ctrl+G or simply navigate to where it is.

    I decide to repurpose Ctrl+[ to do the deed.

    First, I recorded a macro to see what happens when I press Ctrl+[. That became the first line after the error handler. Rather than doing a bunch of checking to see whether the ActiveCell was in a PivotTable, whether the PivotTable source was a Range, and a bunch of other stuff that I can’t even predict, I just attempt to Goto the SourceData. If it works, then it was possible. If there’s some reason I can’t, it just fails silently.

    The Amsterdam Excel Summit 2017

    Hi Excel lovers,

    Wanted to draw your attention to our fourth

    Amsterdam Excel Summit

    April 18 and 19, 2017

    We’ve worked hard to get an exciting line-up of speakers presenting on a wide variety of Excel subjects during our two-day conference.

    So if you’re an Excel power-user, this is one of those one-of-a-kind conferences you cannot afford to miss!

    Registration is now open

    Hope to see you in Amsterdam on April 18 and 19 2017!

    Jan Karel Pieterse, Tony de Jonker

    topexcelclass.com

    AET VBE Tools v1.6.1

    Over the last week, I made some changes to my AET VBE Tools.

    Although still free, I want to ensure they are as good as I can make them for you, before I release a paid version.

    This is what has been done –

    An indenting bug was fixed for Select Case constructs when working with Projects and Modules.

    I also improved the indenting of Add Line Numbers code.

    When adding the date and time to text files being exported, the code has been adjusted to show the correct time.

    I made a change to the Copy Code and Compare Code userforms. Sometimes duplicate file names were showing.

    I edited Highlight Code In Excel so that individual Case statements are also highlighted with Select Case constructs.

    Case Statements

    Note: It is a big help with regards to visibility to see them highlighted this way. Unfortunately I had to hard code that part, so it will only work if your tab settings are set to 4. (In the VBE, Tools, Options, Editor, Tab Width). If I find a way to determine this programatically, I will adjust the code to suit. Alternatively, if you know a way, please leave a comment.

    As a small bonus, I also added the functionality to delete Debug.Assert and Stop to the Cleanup Project code.

    Download the new version (now v1.7) here.

    AET VBE Tools

    Late last year, I started work on a new set of VBE tools. It’s an extension of a code indenter that I made several years ago. I thought it would be nice to update it.

    So far I’m up to Version 1.6. It’s freeware (sorry, now shareware), but I’m starting work on a shareware version (v1.7) that will have more stuff. Anyway, here’s what I blogged about it on my site. Give it a try if you like!

    Access AET VBE Tools by right clicking within the active code pane.

    Here are the tools.

    Indent Code
    Indent code within the active VB project, module, procedure or selected text.

    Add Line Numbers
    Add line numbers to code within the active VB project, module, procedure.

    Delete Line Numbers
    Delete line numbers from code within the active VB project, module, procedure.

    Export Code
    Export code from the active VB project, module, procedure to text files.

    Compare Code
    Select modules from projects.

    Code from both modules will be exported to worksheets in a new workbook.

    Code that exists in one module, but not the other (and vice versa) will be highlighted.

    Copy Code
    You can copy code/modules between projects.

    Standard modules, class modules and userforms will be be replaced if they exist (have the same name), or added if they don’t exist.

    ThisWorkbook code will be replaced.

    Sheet module’s code will be replaced if the sheet exists, or worksheets will be added with the new code if they don’t exist.

    Macro Comment Tools
    A handy way to add generic comments to all macros in the active project or module.

    Insert Code Snippets
    Tired of entering the same code all the time? This makes it easier.

    Run Favourite Macros
    Yes, you can already run them from your Personal workbook.

    But now you can also export your favourite code to text files. Note: Not all code will run. This is a experimental tool, but I have found it quite useful when coding myself.

    Multiple Find And Replace
    You can find and replace code with several fields at once. Fields are saved between sessions.

    Cleanup Project
    Just 2 options at the moment. You can delete lines of code that have “Debug.Print”, and also delete excess blank lines. (Only a single blank line will remain)

    Highlight Code In Excel
    Export your code to a worksheet in a new workbook. Selecting cells in Column A that have keywords like If, With, Select, etc will be highlighted so you can see where that part of the code begins and ends. This is very beneficial to your mental health when trying to figure out what connects with what in those long, long procedures.

    Last, but not least, there are various settings available.

    Download AET VBE Tools v1.6 (v1.7) here.