I have a string with Chr(10) in it.
I want to know the position of this Chr(10) so I can cut off the string starting from this Chr(10).
What function gives this position?
Thx
I have a string with Chr(10) in it.
I want to know the position of this Chr(10) so I can cut off the string starting from this Chr(10).
What function gives this position?
Thx
Function SnipString(rng)
x = Mid(rng, Application.Find(Chr(10), rng) + 1, Len(rng))
Loc = x
End Function
in a worksheetfunction you'd use the search or find function
=SEARCH(CHAR(10),a1)
in VBA:
use the InStr function.
dim iPos%
ipos = instr("where's the linefeed", vblf)
xl2000 and newer also have an InstrRev() function
which search from the end of the string to give you the LAST occurance
of the substring.
note:
vblf is a constant used in VB(a) for chr(10)
vbcr = chr(13)
see VBA help for the full monty.
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam
Zurn wrote :
>
> I have a string with *Chr(10)* in it.
>
> I want to know the position of this Chr(10) so I can cut off the
> string starting from this Chr(10).
>
> What function gives this position?
>
> Thx
David,
i would NOT use the FIND worksheetfunction for this.
it's many times slower than instr.
WHY then len(s)??
- it's optional for vba's mid function.
- it should be len(s) - iPos
Sub USEinstrNOTfind()
Dim s$, r$, n&, m&, t(1)
m = 100000
s = "Find the line" & vbLf & "feed"
t(0) = Timer
For n = 1 To m
r = Mid$(s, Application.Find(vbLf, s) + 1)
Next
t(0) = Format(Timer - t(0), "0.00")
t(1) = Timer
For n = 1 To m
r = Mid$(s, InStr(1, s, vbLf) + 1)
Next
t(1) = Format(Timer - t(1), "0.00")
MsgBox Join(t, vbLf)
End Sub
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam
davidm wrote :
>
> Function SnipString(rng)
> x = Mid(rng, Application.Find(Chr(10), rng) + 1, Len(rng))
> Loc = x
> End Function
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks