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:

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

  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.
    
    
To run the Space Parsing macro, do the following:

  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.


KBCategory: kbother
KBSubcategory:

Additional reference words: 1.00 1.03 1.04 1.06 1.50 noupd


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 1, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.