
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.
Easy to understand flow
ReplyDeleteThank you for sharing. I run into this situation often. I will try this next time. Cheers!
ReplyDelete