-
Notifications
You must be signed in to change notification settings - Fork 2
/
find.bas
37 lines (25 loc) · 840 Bytes
/
find.bas
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
option explicit
sub main() ' {
dim rangeData as range
set rangeData = testData
'
' Determine the position of the vertical bar within the »text«:
'
rangeData.offset(0, 1).formulaR1C1 = "=find(""|"", rc[-1])"
'
' Use the position along with left to extract the porition of »text« that
' is to the left of the vertical bar:
'
rangeData.offset(0, 2).formulaR1C1 = "=left(rc[-2], rc[-1] - 1)"
'
' Also extract the portion of »text« to the right of the vertical bar:
'
rangeData.offset(0, 3).formulaR1C1 = "=right(rc[-3], len(rc[-3]) - rc[-2]) "
end sub ' }
function testData() as range' {
activeSheet.cells.clearContents
cells(1, 1) = "abc|defg hij"
cells(2, 1) = "kl|mnopq|rst"
cells(3, 1) = "uvwx|yz"
set testData = range(cells(1, 1), cells(3, 1))
end function ' }