Technology

A quick trick to build a function that addresses reformatting strings of numbers or text.
Bill JelenDecember 22, 2010

Reader Doug H. wins an autographed copy of Excel for Auditors from CFO and MrExcel for his question: “Is there a way to build a function that addresses reformatting strings of numbers or text? For example, our I-Series software lists dates as an 8-character value/string in the following format, YYYYMMDD. I have to write a long formula as follows:
DATEVALUE(MID(B2,5,2)&”/”&RIGHT(B2,2)&”/”&LEFT(B2,4)).”

Doug continues: “Is there an easier way [to solve this problem], or can I create a function that mirrors my formula instead of re-creating it every time I download information? This could be a huge time saver for me.”

Here’s a simple approach to dealing with Doug’s date-conversion problem.

Solving with Doug’s Formula
Doug is getting data that looks like the column shown in Figure 1. (See all three stages of Doug’s original data below.)

I love Doug’s formidable formula, yet it must be a pain to type. Furthermore, as you see in Figure 2, it provides the right answer in the wrong format. In order to get the right format, you have to choose a Date format from the dropdown on the Home tab of the ribbon as shown in Figure 3.

Fig. 1

Fig. 2

Fig. 3

The Amazing Solution
I was amazed that the following solution actually works. Follow these steps:

1. Select the range of cells containing dates in the YYYYMMDD format.

2. Choose Data, Text to Columns.

3. In Step 1 of the wizard, choose either Delimited or Fixed Width.

4. Click Next twice.

5. In Step 3 of the wizard, open the Date dropdown and choose YMD format as shown in Figure 4 (below).

6. Click Finish.

Fig. 4

The numbers will change to real Excel dates. If the column is not wide enough, as shown in Figures 5 and 6 (below), double-click between the column headings to widen the column. Miraculously, the column of 20101223 values is now replaced with real dates.

Fig. 5

Fig. 6

Bill Jelen is the author of 32 books about Excel, including Guerilla Data Analysis Using Microsoft Excel. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.