Text Troubles

If you work with data from various sources, it's possible you will come across numbers formatted as Text. Depending on what you plan to do with these numbers, it's possible that the Text formatting will cause problems. For example, let's see how many "numbers" there are in the picture below. Look at the formula I've entered in cell B1.



Hmmm. Unlikely? No, I've been handed data apparently "managed" by 2 or more people, with different formatting within the same column. It does happen.

Let's set things right.

Clicking Ctrl + F on your keyboard brings up the Find and Replace dialog box. From there, click Format, then Format again.



Select Text from the Number tab, then click OK.



Click Find All, then type Ctrl + A on your keyboard to select all cells found with Text format.



Bring up the right-click menu with your mouse (or keyboard) and select Format Cells.



Now it's a matter of choosing a more suitable format. As it turns out, the other cells have been formatted as 00000 to show the numbers as 5 digits. To match this, I select Custom on the Number tab, then type in 00000 and click OK.



Yay! Hang on... cell B1 still shows a total of 3. Yikes!



VBA is your friend. Make sure all cells are selected. Type Ctrl + F11 to show the Visual Basic Editor, then Ctrl + G to show the Immediate Window (if not already visible).

Now type

selection.value = selection.value


Click Enter on your keyboard. Now all looks good.



Treat yourself to a well-earned cup of tea or coffee. (The alcohol beverages come after work)

See you next time.