Just a thought to get you started:
let a variable run through the string, if the character it evaluates is not a ",", you delete it. Afterwards, use LEN() function.
I want to count the number of commas in a cell, if any.
tells me the location of the first comma (2), but I want the total number of commas to be returned (7). I've been using a laborious character-by-character check from 1 to len(string) and incrementing the variable CommaCount every time it finds a comma, but I'm pretty sure there's an easier way.VB:instr("A,B,C,D",",")
If the string is in cell A1, use:
Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.
Whoa, this just blew my mind.
Turned a 30+ second operation on 16,000 cells with cell length 1000-1200 characters and searching for an 11-character string into a <1 second operation!
Awesome code...VB:For a = 1 To Range("A" & Rows.Count).End(xlUp).Row If InStr(Range("A" & a).Value, "MtlChgLoc") = 69 Then Range("B" & a).Value = (Len(Range("A" & a).Value) - Len(Replace(Range("A" & a).Value, "Mtl EqMtlId", ""))) / 11 End If Next a
I realise that the conversation is more than a year old. However, I hope this will not be an issue.
So, genuine question: would anyone know whether the next lines of code are likely to run faster than the proposed solutions?
I haven't tried it out yet.VB:Sub main() MsgBox "There are " & countSeparators("A,B,C,D", ",") & " separators" End Sub Function countSeparators(myString As String, mySeparator As String) As Integer countSeparators = UBound(Split(myString, mySeparator)) End Function
I find the following to work well, but I have not tested it for performance/memory impacts:
The split function returns a variant array (zero based). I don't use the contents of the array, just use it as a ruler and check the size of it.VB:Dim Ruler As Variant Dim Counter As Long Ruler = Split("A,B,C,D", ",") Counter = UBound(Ruler) MsgBox Counter
There are currently 3 users browsing this thread. (0 members and 3 guests)