A quick trick to build a function that addresses reformatting strings of numbers or text.
Bill Jelen, CFO.com | US
December 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.