VBA extraction of a part of the cells content

[Jack Sons]

Hi all (is Luke still listening?),

In Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)) I want to extract the
part after position (say) 18 and before position 29.
I forgot what VBA instruction does the trick. Please help because I can't go
on with my work.

Jack Sons
The Netherlands

 

[Dave Peterson]

First, it looks like you can just use:

cells(rijrij, kolkol)

since the range is a single cell.

msgbox mid(cells(rijrij, kolkol).value, 19, 10)

Start with position 19 for a length of 10.

 

[Jack Sons]

Dave,

Thank you.
Why a messagebox? That halts my code while I can program start and end
positions because I know them.
With "I want to extract" I meant that I want a variable (say "thatpart") to
have the result of the extracting.

Can I use

thatpart = mid(cells(rijrij, kolkol).value, 19, 10) ?

Jack.

 

[Dave Peterson]

You didn't share what you wanted to do with the extracted string. So I chose to
use a msgbox to display it.

It's ok with me if you use that.

But the real question is: Did it work for you?

 

[Jack Sons]

Dave,

Yes and no. What I need is the following.

The content of Cells(rijrij, kolkol) is like

Tiele
SHAD.-04-06
06 mei 2010
~gedaagde uitstel

but the format of lines 3 and 4 can differ

1) the first line has no fixed number of characters - it's a name +space
+ char(10),
2) the second line has always the same format (11 characters + space +
char(10)
3) the third line with the date (any date) could also be
06 mei 2010 vonnis
which is a date followed by 3 spaces and the word "vonnis", in this
case there is no fourth line,
if the word "vonnis" is not there the fourth line does exist
4) if the fourth line exists it will always start with ~ followed by 5 to
25 characters

I have a variable D that should be the date in line 3 because I need it in
If DateValue(D) < Date Then

That's all, it should be easy, but I messed around terribly, see the messy
code below the dotted line.

Can you show me the code I need?

Jack.
------------------------------------------------------------------------------------------------------
'til = InStr(1, Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)), "~",
vbBinaryCompare)
'D = Mid(Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)), N + 13, 100)
'D = Replace(D, " vonnis", "")
'LLL = Len(D)
'If D <> "" Then
'If Cells(rijrij, kolkol).Value Like "*~*" Then
'tiltil = InStr(1, D, "~", vbBinaryCompare)
'W = LLL - tiltil

'D = Mid(D, 1, W - 3)
'D = Replace(D, "~*", "")
'lang = Len(Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)) - 9)
'D = Mid(Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)), N + 13, 100)
'Else
'D = Mid(Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)), N + 13, til -
N - 13)
'End If
'End If

 

[Ron Rosenfeld]

Dave,

Yes and no. What I need is the following.

The content of Cells(rijrij, kolkol) is like

Tiele
SHAD.-04-06
06 mei 2010
~gedaagde uitstel

but the format of lines 3 and 4 can differ

1) the first line has no fixed number of characters - it's a name +space
+ char(10),
2) the second line has always the same format (11 characters + space +
char(10)
3) the third line with the date (any date) could also be
06 mei 2010 vonnis
which is a date followed by 3 spaces and the word "vonnis", in this
case there is no fourth line,
if the word "vonnis" is not there the fourth line does exist
4) if the fourth line exists it will always start with ~ followed by 5 to
25 characters

I have a variable D that should be the date in line 3 because I need it in
If DateValue(D) < Date Then

That's all, it should be easy, but I messed around terribly, see the messy
code below the dotted line.

Can you show me the code I need?

Jack.

Jack,

If your date is always in the format of "dd mmm yyyy", and is always on the
third line, then you could use this code to set D = to the date in the third
line, provided, of course, that your regional settings are set properly (Dutch,
I believe).

===============================
Dim D as date
D = Left(Split(Cells(Cells(rijrij, kolkol)), vbLf)(2), 11)
===============================
--ron

 

[Dave Peterson]

Your sample didn't match your description.

Is it:
06 mei 2010 vonnis
or is it:
06 mei 2010(alt-enter)

but as long as you only want the first 11 characters of that line, it won't
matter.

Option Explicit
Sub testme()

Dim myDate As Date
Dim myStr As String
Dim HowManyAltEnters As Long
Dim AltEnterPos As Long
Dim iCtr As Long

myStr = ActiveSheet.Range("a1").Value 'your .cells(....) stuff here

HowManyAltEnters = Len(myStr) - Len(Replace(myStr, vbLf, ""))

'some invalid date -- used as a flag, too.
myDate = DateSerial(9999, 12, 31)
If HowManyAltEnters < 2 Then
'there is no 3rd line
'so don't look and don't change the invalid date
Else
'chop the first two lines
For iCtr = 1 To 2
AltEnterPos = InStr(1, myStr, vbLf, vbTextCompare)
myStr = Mid(myStr, AltEnterPos + 1)
Next iCtr

'take the next 11 characters
myStr = Left(myStr, 11)
'just to prove that it's working ok
MsgBox myStr
On Error Resume Next
myDate = CDate(myStr)
If Err.Number <> 0 Then
Err.Clear
'mydate didn't change from 12.31.9999
'just to prove it
MsgBox myDate
End If
End If

If Year(myDate) = 9999 Then
MsgBox "No date!"
Else
MsgBox Format(myDate, "mmmm dd, yyyy")
End If

End Sub

Dave,

Yes and no. What I need is the following.

The content of Cells(rijrij, kolkol) is like

Tiele
SHAD.-04-06
06 mei 2010
~gedaagde uitstel

but the format of lines 3 and 4 can differ

1) the first line has no fixed number of characters - it's a name +space
+ char(10),
2) the second line has always the same format (11 characters + space +
char(10)
3) the third line with the date (any date) could also be
06 mei 2010 vonnis
which is a date followed by 3 spaces and the word "vonnis", in this
case there is no fourth line,
if the word "vonnis" is not there the fourth line does exist
4) if the fourth line exists it will always start with ~ followed by 5 to
25 characters

I have a variable D that should be the date in line 3 because I need it in
If DateValue(D) < Date Then

That's all, it should be easy, but I messed around terribly, see the messy
code below the dotted line.

Can you show me the code I need?

Jack.
------------------------------------------------------------------------------------------------------
'til = InStr(1, Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)), "~",
vbBinaryCompare)
'D = Mid(Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)), N + 13, 100)
'D = Replace(D, " vonnis", "")
'LLL = Len(D)
'If D <> "" Then
'If Cells(rijrij, kolkol).Value Like "*~*" Then
'tiltil = InStr(1, D, "~", vbBinaryCompare)
'W = LLL - tiltil

'D = Mid(D, 1, W - 3)
'D = Replace(D, "~*", "")
'lang = Len(Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)) - 9)
'D = Mid(Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)), N + 13, 100)
'Else
'D = Mid(Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)), N + 13, til -
N - 13)
'End If
'End If

 

[Jack Sons]

Thank you Ron.

I never saw the split function before. My VBA help is not very helpful. For
instance it seem not to know the delimiter vbLf. What is the meaning of it?
If I understand the code, the part with
(2), 11)
belongs tot the Left function. But why (2)? Why not 2? What does it mean?
And why 11?
06 mei 2010 may have 11 characters, but it could be any date, so also 29
december 2010 which has 16 characters.

You see, I don't understand. Please shed light on it.
Thanks in advance.

It's 04.10 in the morning here in The Hague, I go to bed now.
Hope I see your answer when I get up.

Jack.

 

[Jack Sons]

Thank you Dave.

I am overwhelmed by your code, is what seemde so simple (to me) really that
complicated?
By the way, I noticed message boxes again, but I do not want the code tot do
that because it functions in do loops which could show easily 12 × 10 times
such a message box.

Then about your questions
it is not 06 mei 2010(alt-enter)
but
06 mei 2010 vonnis
so on one line.

but as long as you only want the first 11 characters of that line, it
won't

Oops, 06 mei 2010 has 11 characters but bit could be any date so also 29
december 2010
which has 16 characters

You see, I don't understand it fully. Please shed light on it.
Thanks in advance.

It's 04.20 in the morning here in The Hague, I go to bed now.
Hope I see your answer when I get up.

Jack.

 

[Ron Rosenfeld]

Thank you Ron.

I never saw the split function before. My VBA help is not very helpful. For
instance it seem not to know the delimiter vbLf. What is the meaning of it?
If I understand the code, the part with
(2), 11)
belongs tot the Left function. But why (2)? Why not 2? What does it mean?
And why 11?
06 mei 2010 may have 11 characters, but it could be any date, so also 29
december 2010 which has 16 characters.

You see, I don't understand. Please shed light on it.
Thanks in advance.

It's 04.10 in the morning here in The Hague, I go to bed now.
Hope I see your answer when I get up.

Jack.

Split is a useful function.

It breaks up a string using whatever delimiter you care to use. In this case,
I used vbLf which is chr(10) or <alt-Enter> This is listed in Help under
Visual Basic Constants
Miscellaneous Constants.

But, if Dutch uses a different constant, you could either use that, or just use
Chr(10) for the delimiter.

Split results in a 0-based array; and the (2) refers to Element #2 in that
array; which would be the third line
(0) Line 1
(1) Line 2
(2) Line 3

From your description, Line 4 is irrelevant so can be ignored.

Not knowing your language, I did not realize that your date format is "dd mmmm
yyyy". That being the case, we cannot used a fixed length for the date string,
but we could test for the presence of the 4 digits.

So, something like:

==================================
Option Explicit
Sub foo()
Dim c As Range, rg As Range
Dim v() As String
Dim D As Date
Dim i As Long

Set rg = Range("A1:A2")
For Each c In rg
v = Split(c.Value, vbLf)
'test for position of year
'year will be first number > 1900
For i = 1 To Len(v(2))
If Val(Mid(v(2), i)) > 1900 Then
i = i + 4
Exit For
End If
Next i
'------------------------
D = Left(v(2), i)
Debug.Print D
Next c
End Sub
=====================================

Note that I did some rudimentary error checking -- there must be at least three
lines in the string we are testing; and there must be a valid date in Line
three.

For determining the length of the string, I looked for the first value in Line
3 that was greater than 1900.

I also expanded the one line, to make it more clear what was going on with the
Split function.
--ron

 

[Ron Rosenfeld]

Split is a useful function.

It breaks up a string using whatever delimiter you care to use. In this case,
I used vbLf which is chr(10) or <alt-Enter> This is listed in Help under
Visual Basic Constants
Miscellaneous Constants.

But, if Dutch uses a different constant, you could either use that, or just use
Chr(10) for the delimiter.

Split results in a 0-based array; and the (2) refers to Element #2 in that
array; which would be the third line
(0) Line 1
(1) Line 2
(2) Line 3

From your description, Line 4 is irrelevant so can be ignored.

Not knowing your language, I did not realize that your date format is "dd mmmm
yyyy". That being the case, we cannot used a fixed length for the date string,
but we could test for the presence of the 4 digits.

So, something like:

==================================
Option Explicit
Sub foo()
Dim c As Range, rg As Range
Dim v() As String
Dim D As Date
Dim i As Long

Set rg = Range("A1:A2")
For Each c In rg
v = Split(c.Value, vbLf)
'test for position of year
'year will be first number > 1900
For i = 1 To Len(v(2))
If Val(Mid(v(2), i)) > 1900 Then
i = i + 4
Exit For
End If
Next i
'------------------------
D = Left(v(2), i)
Debug.Print D
Next c
End Sub
=====================================

Note that I did some rudimentary error checking -- there must be at least three
lines in the string we are testing; and there must be a valid date in Line
three.

For determining the length of the string, I looked for the first value in Line
3 that was greater than 1900.

I also expanded the one line, to make it more clear what was going on with the
Split function.
--ron

Wrong code pasted above. The difference is error checking:

Should be:

=======================================
Option Explicit
Sub foo()
Dim c As Range, rg As Range
Dim v() As String
Dim D As Date
Dim i As Long

Set rg = Range("A1:A3")
For Each c In rg
v = Split(c.Value, vbLf)

'Make sure there are at least three lines
If UBound(v) < 2 Then
Debug.Print "Less than three lines"
Exit Sub
End If

'test for position of year
'year will be first number > 1900
For i = 1 To Len(v(2))
If Val(Mid(v(2), i)) > 1900 Then
i = i + 4
Exit For
End If
Next i
'------------------------
If IsDate(Left(v(2), i)) Then
D = Left(v(2), i)
Debug.Print D
Else
Debug.Print "Invalid Date in Line 3: " & v(2)
End If
Next c
End Sub
=============================================
--ron

 

[Dave Peterson]

The msgbox lines are only there to show you that the code is working. You can
delete them when you're positive that the code is doing what it's supposed to.

Thank you Dave.

I am overwhelmed by your code, is what seemde so simple (to me) really that
complicated?
By the way, I noticed message boxes again, but I do not want the code tot do
that because it functions in do loops which could show easily 12 × 10 times
such a message box.

Then about your questions
it is not 06 mei 2010(alt-enter)
but
06 mei 2010 vonnis
so on one line.

but as long as you only want the first 11 characters of that line, it
won't

Oops, 06 mei 2010 has 11 characters but bit could be any date so also 29
december 2010
which has 16 characters

You see, I don't understand it fully. Please shed light on it.
Thanks in advance.

It's 04.20 in the morning here in The Hague, I go to bed now.
Hope I see your answer when I get up.

Jack.