Numbers stored as text
Numbers stored as text can be irritating, whether it’s just for aesthetics or these mixed numbers making your VLookUps misbehave. The following fix may seem strange to start out, but it will have all your numbers working like numbers in no time.
- Type a 1 in a blank cell and copy it using Ctrl+C, right-clicking Copy or selecting the Copy button on the ribbon (Home tab, Clipboard group).
- Select the column of mixed numbers and numbers stored as text.
- Click the dropdown arrow below the Paste button, and choose Paste Special.
- Click the radio button next to Multiply in the Operations section of the Paste Special dialog box.
Voila! All numbers!
Zero front-filled numbers
If you’ve just done the operation above or imported data from another system that leaves off required leading zeroes, try this operation to put them back. Caution: This particular method works only with fixed-length fields.
- Selec...(register to read more)