A few weeks ago, Steve Endow retweeted about formatting a number in Microsoft Excel into a Microsoft Dynamics GP account number (I can’t find the tweet now, unfortunately). I had a fiddle around with this and it works fine for display, but the underlying data is still a number; it is just the display which has been changed to a formatted number.
However, with only a couple further steps, the account number can be correctly reformatted. I’m going to step through this one from the start; the basic premise is that we have some account strings which are not formatted; e.g. they do not have the segment separators or leading zeros:
Select the data to be formatted, right click on the data and select Format Cells from the context menu.
Set the Category to Custom and enter your account format in the Type field:
Click OK and the data will be formatted:
Click one of the reformatted cells; the formula bar will show that the underlying data is exactly the same as it was originally:
The solution is a couple of extra steps, but it is fairly straightforward. Select the reformatted data and paste it iinto Notepad:
Copy the data from Notepad and paste back over the data in Excel. You might be prompted that the data isn’t the same size as the section; click OK to complete the paste:
If you again check the data as shown in the formula bar, you’ll see that the data is now fully reformatted:
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.