Excel: Space Parsing Macro for Versions 1.50 and Earlier

Last reviewed: November 1, 1994
Article ID: Q51965

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     |
                             ----------------

If you place the cursor in cell A1 and press COMMAND+OPTION+A to run the Space Parsing macro, the result is the following:

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

To create the Space Parsing macro, do the following:

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]")

12 | Firsthalf | =MID(DEREF(ACTIVE.CELL()),1,DEREF(Firstspace)-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()

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.

To run the Space Parsing macro, do the following:

Load Microsoft Excel.
Open both the unconverted spreadsheet (or the spreadsheet that contains the data that requires parsing) and the Space Parsing macro.
Place the cursor on the first element of data on the top of the column that requires conversion (or parsing).
Press COMMAND+OPTION+A. The macro converts the single column into multiple columns of data.

KBCategory: kbother

KBSubcategory:
Additional reference words: 1.00 1.03 1.04 1.06 1.50 noupd

Microsoft KB Archive/51965

Excel: Space Parsing Macro for Versions 1.50 and Earlier

SUMMARY

MORE INFORMATION