Reformat Number into Dynamics GP Account Number

Microsoft ExcelA 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:

Microsoft Excel with unformatted data

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:

Format Cells

Click OK and the data will be formatted:

Microsoft Eccel with formatted data

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:

Notepad with reformatted data pasted

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:

Date size warning

If you again check the data as shown in the formula bar, you’ll see that the data is now fully reformatted:

Excel with data fully reformatted

What should we write about next?

Your Name (required) –
Your Email (required) –

Leave a Reply

Your email address will not be published. Required fields are marked *