Excel: Space Parsing Macro for Versions 1.50 and Earlier

SUMMARY

Outlined below is a Microsoft Excel macro for parsing spaces. This macro takes data from a single cell and "parses" it among several cells. For example, a cell containing the information "data data data data" would be separated into four cells with the Space Parsing macro.

Note: Excel version 2.20 has a built-in parsing command.

MORE INFORMATION

The Space Parsing macro assumes that you have two or more spaces between the items you want parsed into separate cells. Below is an example of how the Space Parsing macro separates data.

Assume that the original data is as follows:

                                    A
                             ----------------
                          1 |   234    234   |
                             ----------------
                          2 |  12   12   12  |
                             ----------------
                          3 |     23  23     |
                             ----------------

                        |    A   |    B   |    C   |
                     -------------------------------
                      1 |   234  |   234  |        |
                     -------------------------------
                      2 |    12  |    12  |    12  |
                     -------------------------------
                      3 |        |    23  |    23  |
                     -------------------------------

1. Type the macro into a macro sheet as follows:

      |      A     |                         B
   

 1 | Names      | SPACEPARSER

 2 |            | =ECHO(FALSE)

 3 | Firstspace | =SEARCH("  ",DEREF(ACTIVE.CELL()),1)

 4 |            | =IF(ISERROR(Firstspace),GOTO(Next))

 5 |            | =SET.NAME("endspace",DEREF(Firstspace))

 6 | Startloop  | =IF(SEARCH("  ",DEREF(ACTIVE.CELL()),endspace+1)=
   |            |  (endspace+1),SET.NAME("endspace",endspace+1))

 7 |            | =IF(Startloop<>FALSE(),GOTO(Startloop))

 8 | Secondhalf | =MID(DEREF(ACTIVE.CELL()),endspace+2,
   |            |  (1+LEN(ACTIVE.CELL())-endspace))

 9 |            | =SELECT("rc[1]")

10 |            | =FORMULA(Secondhalf)

11 |            | =SELECT("rc[-1]")

13 |            | =FORMULA(Firsthalf)

14 |            | =SELECT("rc[1]")

15 |            | =GOTO(SPACEPARSER)

16 | Next       | =SELECT("rc[-"&COLUMN(ACTIVE.CELL())-1&"]")

17 |            | =SELECT("r[1]c")

18 |            | =IF(NOT(AND(TYPE(ACTIVE.CELL())=1,
   |            |  LEN(ACTIVE.CELL())=0)),GOTO(Firstspace))

19 |            | =RETURN()

1. After typing in the macro, do the following:

   a. The cell names in column A must be applied to the cells in

         column B. To apply the names, do the following after typing in
         the macro:
   

         1) Select from A1 to B19 with your mouse, and choose Create
            Names from the Formula menu.
   
         2) In the dialog box, check Left Column and click OK. Be sure
            the names in column A are typed just as they appear.
   
      b. The macro will not run until it is named. To name the macro, do
         the following:
   
         1) Select cell B1, and choose Define Name from the Formula
            menu. Under Name, it should say "SPACEPARSER". Under Refers
            To, it should say "=$B$1".
   
         2) Under Macro, click Command and type the letter "A" in the box
            beside Option+Command Key, then click OK.
   
       c. Make sure you placed two spaces between the quotation marks in
          cells B3 and B6. If you typed only one space between these
          quotation marks, the macro will truncate or cut off some of
          your data.
   
   

1. Load Microsoft Excel.

2. Open both the unconverted spreadsheet (or the spreadsheet that contains the data that requires parsing) and the Space Parsing macro.

3. Place the cursor on the first element of data on the top of the column that requires conversion (or parsing).

4. Press COMMAND+OPTION+A. The macro converts the single column into multiple columns of data.