Parse Range in Microsoft Excel Add-in with code example

Parse

Method splits cell data and parse it base on given pattern. Parse method parse selected cells into a destination range. Parse method exposed by Range object.

Syntax

Range.Parase(ParaseLine, Destination)

Parameters

1. ParaseLine: It is an optional parameter which is variant in nature, It takes a pattern as [xxx] where each “x” character refers a value to be parsed and “[]” bracket shows separator that needs to be applied in the text.
2. Destination: It is an optional parameter which is variant in nature, It takes a range argument where actual parse result will be populated

Example

Consider below data where we have “-” separated values. The parse method can separate each value by eliminating “-“ as shown below in output.

Data Source

Code example

Public Sub ParseExample()
    'Declare range object
    Dim oRange As Range
    
    'Bind selection to range
    Set oRange = Selection
    
    'Parase each string
    oRange.Parse parseLine:="[xxx] [xxx] [xxxx]", Destination:=Range("B1")
    
    'Cleanup
    If Not oRange Is Nothing Then
        Set oRange = Nothing
    End If
End Sub

Output

Explanation

In code we have given pattern as “[xxx] [xxx] [xxxx]” which determines as:

1. [xxx]: pick three values from string i.g. 200, 200, 900
2. [xxx]: pick next three values from string i.g. 778, 768, 786
3. [xxxx]: picks last four values from string i.g. 1818, 1819, 1819

The above pattern can be modified as per your need to parse string.

Share on Facebook

Tweet