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
Very elegant. And... massively useful for zapping a disparate range of cells! Thanks!
VB:Sub ZapWorksheetCells() Dim arrCells() As String Dim intN As Integer arrCells = Split("b5,g5,i5,a8,a10,l8,l9,l10,o8,o9,o10,q8,q9,q10,a14,c17:m17,c18:q28,a22:a28,a32:q38,a41,f45:f46,s45,s46,t45,t46,a53:q60,a64:k71,a80:k97,d104:o115", ",") For intN = 0 To UBound(arrCells) Range(arrCells(intN)).Select 'do some stuff Next intN End Sub
There are currently 3 users browsing this thread. (0 members and 3 guests)