473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Colon as a Delimiter

I am trying to take a string (specifically one in the format "2008-09-01 00:00") and write an if statement to perform a process if the value to the right of the rightmost colon is anything but "00"

I have extracted the text from the cell using the code:

Expand|Select|Wrap|Line Numbers
  1. For h = 2 To NumberOfRows
  2.   Cells(h, 1).Select
  3.   Dim v As Variant
  4.   Mytext = Cells(h, 1).Text
"Mytext" displays "2008-09-01 00:00"

I do not know how to use a colon as a delimiter to find the value of the last two values in the string. Would I use the "Split()" function?
Sep 29 '11 #1

✓ answered by ADezii

Yet another approach should you have multiple Colons in the String:
Expand|Select|Wrap|Line Numbers
  1. Dim strTest As String
  2.  
  3. strTest = "2008-09-01 00:100"
  4.  
  5. 'See if the Value to the right of last Colon <> "00"
  6. If Mid$(strTest, InStrRev(strTest, ":") + 1) <> "00" Then
  7.   'Process Code here
  8. End If

11 4565
Rabbit
12,516 Expert Mod 8TB
Split could be used. You could also use InStrRev in conjunction with a Right.
Sep 29 '11 #2
patjones
931 Expert 512MB
Split( ) actually returns an array, so you need to Dim an array and then do the assignment...

Expand|Select|Wrap|Line Numbers
  1. Dim arr() As String
  2. arr = Split(MyText, ":")

You can then reference the array elements as you wish.

Pat
Sep 29 '11 #3
Rabbit
12,516 Expert Mod 8TB
If you only need to use it once and you know which element you want, you can do this
Expand|Select|Wrap|Line Numbers
  1. Split(MyText, ":")(1)
Sep 29 '11 #4
ADezii
8,834 Expert 8TB
Yet another approach should you have multiple Colons in the String:
Expand|Select|Wrap|Line Numbers
  1. Dim strTest As String
  2.  
  3. strTest = "2008-09-01 00:100"
  4.  
  5. 'See if the Value to the right of last Colon <> "00"
  6. If Mid$(strTest, InStrRev(strTest, ":") + 1) <> "00" Then
  7.   'Process Code here
  8. End If
Sep 29 '11 #5
NeoPa
32,556 Expert Mod 16PB
Austin Redd:
I do not know how to use a colon as a delimiter to find the value of the last two values in the string. Would I use the "Split()" function?
I'm not sure what you mean by the value of the last two values in the string, as you've only explained a requirement to see the value after the colon. I guess you probably mean last two digits. If so, the following code, as explained by Rabbit in post #4, will give you access to that :
Expand|Select|Wrap|Line Numbers
  1. Split(MyText, ":")(1)
Sep 29 '11 #6
ADezii
8,834 Expert 8TB
@NeoPa: that logic would not work for the 'rightmost' Colon, as indicated by:
write an if statement to perform a process if the value to the right of the rightmost colon is anything but "00"
This would indicate to me that there can be more than 1 Colon in the String. Off Tangent again? (LOL)
Sep 30 '11 #7
Mihail
759 512MB
Expand|Select|Wrap|Line Numbers
  1. Dim LastCharacters As String
  2.  
  3. For i = Len(MyText) To 1 Step -1
  4.     If Mid(MyString, i, 1) = ":" Then 'The character number i is ":"
  5.         LastCharacters = Mid(MyString, i+1)
  6. Exit For
  7.     End If
  8. Next i
  9.  
  10. Debug.Print LastCharacters
Sep 30 '11 #8
NeoPa
32,556 Expert Mod 16PB
That's good critical thinking ADezii, but in this case I expect it's just a case of the OP being unable to express their question clearly enough. Notice the following :
Austin Redd:
(specifically one in the format "2008-09-01 00:00")
I worked on the basis that what they'd meant to say was :
write an if statement to perform a process if the value to the right of the colon (found towards the right-hand end of the string) is anything but "00"

If they hadn't already stated specifically what the expected format was then your querying this would be well worth raising and the code maight well need to be done differently. This code only handles the minutes value and never any seconds value if that were to be included.
Sep 30 '11 #9
patjones
931 Expert 512MB
If the format of the string is always as shown in the OP's example, then I really think this is a simple matter of using Split( ) in the manner that Rabbit indicates in post 4.

On the other hand, if the format has a varying number of delimiters, in some fashion that the OP has not pointed out to us yet, then it makes good sense to use an array as I show in post 3. This way, regardless of whether the string splits into two, five, or fifteen smaller parts (or whatever), you can always just reference the last element of the array to obtain the rightmost part of the parent string.

Pat
Sep 30 '11 #10
NeoPa
32,556 Expert Mod 16PB
As the OP used the word explicitly in describing the format of "2008-09-01 00:00" I doubt that is the case. Assuming it were however, you might instead use :
Expand|Select|Wrap|Line Numbers
  1. Split(MyText, ":")(UBound(Split(MyText, ":")))
You could store it into an array first to save running the function procedure twice.
Sep 30 '11 #11
Thank you ADezii. That gave me what I needed to work with. The code I used was this:

Expand|Select|Wrap|Line Numbers
  1. 'find the last row in the "A" column
  2. numberofrows = Range("A65536").End(xlUp).Row
  3. 'write a for statement to iterate through every row except for the column header row (A1)
  4. For h = 2 To numberofrows
  5.  
  6. 'Use my iteration variable "h" to search through the text in each cell
  7. Cells(h, 1).Select
  8. Dim MyText As String
  9. MyText = Cells(h, 1).Text
  10.  
  11. 'If the last two digits in "MyText" are not "00"...
  12. If Mid$(MyText, InStrRev(MyText, ":") + 1) <> "00" Then
  13.  
  14.     'Place zeros at the end of the two cells above the "Hth" row
  15.     'Use these cells to preform an autofill operation
  16.     'This replaces the last two values in every cell below
  17.     Cells(h - 2, 1).Select
  18.     Dim MyText2 As String
  19.     MyText2 = Cells(h - 2, 1).Text
  20.     NewText = Mid$(MyText2, InStrRev(MyText2, ":") - 13)
  21.     Cells(h - 2, 1).Value = NewText & ":00"
  22.     Cells(h - 1, 1).Select
  23.     Dim MyText1 As String
  24.     MyText1 = Cells(h - 1, 1).Text
  25.     NewText2 = Mid$(MyText1, InStrRev(MyText1, ":") - 13)
  26.     Cells(h - 1, 1).Value = NewText2 & ":00"
  27.     Range(Cells(h - 2, 1), Cells(h - 1, 1)).Select
  28.     Selection.AutoFill Destination:=Range(Cells(h - 2, 1), Cells(numberofrows, 1)), Type:=xlFillDefault
  29. End If
  30. Next h
Sep 30 '11 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: AMT2K5 | last post by:
Hello, how would I go about breaking up a string that is returned by a function. After I do that, I will strcpy that data to a class data member . I have the following functions void...
1
by: jw56578 | last post by:
I've seen some system sp's that have a semi colon and number after the the name such as create procedure sp_procedure_params_rowset;2 what does this do?
4
by: Raquel | last post by:
Could someone explain to me what the reason is for having a character delimiter (which is double quotes by default) for performing Loads/Imports on UDB? I would think that column delimiter along...
16
by: Raj Kotaru | last post by:
Hello all, I recently came across the following segment of code that defines a C struct: typedef struct { unsigned char unused_bits:4; unsigned char wchair_state:2; } xyz;
3
by: Calvin Lai | last post by:
Hi all, I was developing an web application where the data in the client are stored in cookies using the Response.Cookies collection. However, I have found out that for some clients, their...
161
by: Dan Lenski | last post by:
Hi all, I'm a recent, belated convert from Perl. I work in a physics lab and have been using Python to automate a lot of measurement equipment lately. It works fabulously for this purpose. ...
9
by: martinskou | last post by:
Hi, I'm looking for something like: multi_split( 'a:=b+c' , ) returning: whats the python way to achieve this, preferably without regexp? Thanks.
19
by: smugcool | last post by:
HI ALL, I am having multiple .csv files in a single directry. I wanted to import all these files in a excel file with semicolon delimeted by a single click of a button. Can anyone please help me...
3
by: nigel | last post by:
Hi, I'm using VBA to export data from a table direct to a CSV file DoCmd.TransferText acExportDelim, , "ExportTable", filePath this produced a file with COMMA separated values,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.