Insert Space before Capital Letter

[sweater_vests_rock]

I have data that comes into a sheet like

CountryCode | DateLastAccessed |

I would like to insert spaces before each capitalized letter. in the example,

Country Code | Date Last Accessed |

I've thought about using the Chr code in VBA, looping through each string, but I feel like this could be handled with more finesse. Ideas?

Thanks. Ben.

 

[NateO]

Hello,

I've thought about using the Chr code in VBA, looping through each string, but I feel like this could be handled with more finesse. Ideas?

Not all loops are created equally, it really depends what you are looping through, looping through a range is slower than recordset, which in turn is slower than looping through an array, generally speaking.

So when you say finesse, are you speaking to performance? I personally wouldn't want to use Chr on every character pass (I know, I know... I have posts in the past that do this, I have reconsidered since...).

Have a look at the following, no use of Chr() (or a variant of Chr(), e.g., ChrW$() ), no space on a capital following a space character, and it performs very well (and I mean very well) when tested against the RegExp approach posted to this thread, with all due respect.

Public Function InsSpaceB4Cap2(ByRef myStr As String) As String
Dim bIn() As Byte, bOut() As Byte
Dim i As Long, j As Long
Const lngBase As Long = 2
If Len(myStr) = 0 Then Exit Function
Let bIn = myStr
ReDim bOut(0 To UBound(bIn) * 2)
Let bOut(0) = bIn(0)
Let j = lngBase
For i = lngBase To UBound(bIn) Step 2
    Select Case bIn(i)
        Case 65 To 90
            If bIn(i - 2) <> 32 Then
                Let bOut(j) = 32
                Let bOut(j + 2) = bIn(i)
                Let j = j + 4
            Else
                Let bOut(j) = bIn(i)
                Let j = j + 2
            End If
        Case Else
            Let bOut(j) = bIn(i)
            Let j = j + 2
    End Select
Next
ReDim Preserve bOut(0 To j)
InsSpaceB4Cap2 = bOut
End Function

Sub foo()
Debug.Print InsSpaceB4Cap2("TestThisPlease")
Debug.Print InsSpaceB4Cap2("TestThis Please")
End Sub

Note, I've only given this minimal consideration/testing.

Another benefit to this approach is that it will perform very well on large strings as well, versus concatenating, which should show performance degredation as your string grows in length.

 

[sweater_vests_rock]

Nate.

Thanks so much for your response.

I guess I would characterize my usage of 'finesse' here to mean the opposite of 'brute force', which is how I would describe looping through every letter, of every string, or every cell -- this is the technique which I immediately came upon. Speed plays a factor (as you note, no loops is generally faster than a loop), but beyond that, I wondered if there was a solution that worked, worked well, and didn't immediately present itself.

I certainly don't want to take away from anyone's codes, as each example utilized an idea or two (or many!) that I probably would have missed in my mind-numbing solution. In the end, this was the essence of my question: how can I learn something new while solving a problem. So kudos to you all, and thanks for your excellent work here and elsewhere on this board.

cheers. ben.

I've thought about using the Chr code in VBA, looping through each string, but I feel like this could be handled with more finesse. Ideas?

Not all loops are created equally, it really depends what you are looping through, looping through a range is slower than recordset, which in turn is slower than looping through an array, generally speaking.

So when you say finesse, are you speaking to performance? I personally wouldn't want to use Chr on every character pass (I know, I know... I have posts in the past that do this, I have reconsidered since...).

 

[NateO]

You're welcome, Ben.

Okay, I see. What you are calling 'Brute Force', which I think of as a random attack, with no upper boundary, is probably better described a linear, or sequential, search algorithm. That is, you line up your elements and process them one by one, along your line. This line is finite, however.

It's probably the most common type of algorithm, VLookup() uses it when you pass a False argument. It uses a Binary search when you pass a True argument (4th), but the requirement is that your array must be sorted.

This is not the case here, your array of characters, and the ones you want to isolate are random, and sorting does you no good.

RegExp looks like it's doing less work because all of the underlying code is buried in the Class, but I'd have to bet it's using a linear search as well, as it too must evaluate all characters in your string, or array of characters... Proof's in the pudding, though. Check out the timing on RegExp vs. my Byte Array search/manipulation implementation.

To be fair, though, the approach posted in this thread is doing something you really do not want to do, and that is binding with RegExp on every single function call. That's very expensive, with all due respect, no good for a UDF used over a large Range.

The linear algorithm is not inherently bad in this case, minimizing the iteration and expensive ops (complex function calls), in other words, optimizing it, is the trick. Here's an example:

http://www.mrexcel.com/board2/viewtopic.php?p=804807#804807

Note how you only have to iterate through half of the array, versus the entire thing!

Some thing, here. We do not need to cycle through the entire 2nd Byte array, which is constructed with a buffer, so that we can avoid ReDim Preserve, which is expensive, in a loop. We also ditched strConv() as it too is expensive...

The other trick to consider in implementing a linear search algo, is to consider what you're iterating through. A Range is slow, very fat in memory, look at what the Object in question has to support in terms of properties! The Byte Array is light in terms of memory, limited in terms of what it can support, but extremely fast to iterate through, as a result.

If you're going to have a go at a large Range of Strings with this, I'd personally stick with performance as one of your main objectives.

Food for thought.