Parse Method

[xenou]

Discovered (by accident) just now this method (using XL2003):

Sub foo()
    [COLOR="Red"]Range("A1").Parse[/COLOR]
End Sub

Never heard of it before or seen it used ... anyone else?
Cheers,
ξ

 

[bjurney]

If you ever use the text to columns feature in the Data tab, it will say Parse in the status bar as it works on it. Just means that it will break up a string in a cell into several cells based on your criteria.

 

[xenou]

Okay, excellent. So that's what parsing is. Now, there is a range method called TextToColumns and a range method called Parse. I'll assume that they are not the same (though they could be). I'm being lazy - I have read the help file on Parse but undertook no further investigation beyond that.

I guess my real question is, "has anybody every used this method or found it to be useful..." Since I never noticed it until just yesterday I'm just intrigued so I figured I throw it out there...

 

[Peter_SSs]

I also only became aware of this reasonably recently when I came across the following thread where PGC put it to clever use.
http://www.mrexcel.com/forum/showthread.php?t=423753#10

 

[MrKowz]

It would appear that whatever Parse can do, TextToColumns can do; but not vice-versa. Parse does not have the ability to use a user-defined delimiter; however what I can see that is nice about Parse is that you can easier define where to split the data for a shorter Fixed Width parsing, such as Phone Numbers, SSN, etc.

TextToColumns requires that you use a series of array(x,y) where x is the character to begin at, and y is the data type to store the value.

So, to split 123-456-7890 into three columns, one with area code, one with the 3-digit prefix, and the last with the 4-digit suffix; text to columns would require you to state that split as Array(Array(0, 1), Array(3, 9), Array(4, 1), Array(7, 9), Array(8, 1)). Parse would require you to state it as "[xxx] [xxx] [xxxx]".

A few things to note here:

1. Parse will only work on strings.
2. When stating the ParseLine in parse, it is a literal string comparison where the left/right brackets define what to import. So, if we were to look at the string "123-456-7890", we want to split it as shown above. Looking at the string, we can imagine brackets like "[123]-[456]-[7890]" (we don't want to import those pesky hyphens). Anything outside of the brackets becomes a null space, anything inside becomes an x: "[xxx] [xxx] [xxxx]".
3. Parse can logically guess how you want to split your data if it appears to have some sort of delimiting factor (based on the item in the top left cell)

Hope that sheds some light on the Parse method a bit. Cheers!

 

[Peter_SSs]

It would appear that whatever Parse can do, TextToColumns can do

I can't see how Text To Columns could do the task in the thread I linked to?

 

[MrKowz]

I can't see how Text To Columns could do the task in the thread I linked to?

You are correct - I was in the process of writing my post and hadn't seen your link prior to posting. Very interesting find!