Getting PARSE to Guess in an Excel Macro

ID: Q51721

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

Microsoft Excel 2.x and 3.0

The Data Parse command has a guess feature that, when activated, makes an intelligent guess as to where the breaks should be made in the parse data. The macro-equivalent command, PARSE, has no such option. The following macro code segment takes the active cell or cells and parses them using Excel's guess feature:


   =SEND.KEYS("%(g)~")
   =PARSE?()

The PARSE? command can be used to bring up the standard Data Parse dialog box. Using the SEND.KEYS("%(g)~") command places the key sequence ALT+G, ENTER into the Windows keyboard buffer. When PARSE? is executed, the keyboard buffer is polled for the next available piece of information. Because the ALT+G, ENTER sequence is already in the keyboard buffer, no dialog box is displayed, but the guess feature is executed.

MORE INFORMATION

Microsoft Excel 4.0

Microsoft Excel version 4.0 has the ability to guess built-in, so the SEND.KEYS workaround is not needed in an Microsoft Excel version 4.0 macro.

To say that this problem has been fixed in version 4.0 is somewhat misleading. You cannot force the Parse function to guess again once you have used it. In other words, once you use the function it will attempt to revert to the guess it made when it was first used, for all subsequent uses. You cannot force it to make a new guess in the same session, even if you leave the parse_text (delimiter) argument blank.

It is true that when you leave the parse_text (delimiter) argument blank, it forces Microsoft Excel to guess. However, that in itself is not what the guess button does. When you do a Data Parse manually, Microsoft Excel will always guess on the first parse. The guess button is only used on subsequent parses in the same session, when the delimiter has changed and you need Microsoft Excel to render a new guess. The Guess button is really a "Guess Again" button. Unfortunately, the Parse function has no argument to force Microsoft Excel to guess again, so you still must use this workaround.

Microsoft Excel 5.0

The information above also applies to Microsoft Excel version 5.0 only when using version 4.0 macro language. The preferred method, however, is to use the text-to-columns function.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, page 314
"Microsoft Excel Function Reference," version 3.0, page 175
"Microsoft Excel Functions and Macros," version 2.x, page 327

Additional query words:

Keywords :
Version :
Platform :
Issue type :