0 votes
in VBA by Expert (892 points)
Hello everyone

I have some texts in column A with the same length (say three characters) like "abc"

In the adjacent columns I need to have space in between the string in different positions

Examples of the results for "abc"

a bc

a b c

ab c

1 Answer

+1 vote
by Skilled (477 points)

Hi Yasser,

The following macro is general-purpose. the key portion is the SELECT CASE, where you can define any type of transformation.

By using Offset in conjunction with CASE, the columns are matched to the specific split.

Public Sub test()
    Const NumberOfSplits = 3 ' Assuming you have more than 3 columns of splits

    Dim LastRow As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim FullString As String
    Dim Burst As String ' holds string with spaces
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To LastRow
        With Range("A1").Offset(i - 1, 0)
            FullString = .Value
            ' For each split, define a short case statement, as shown

            For j = 1 To NumberOfSplits
                Select Case j
                    Case Is = 1 ' a_bc
                        Burst = Replace(FullString, Left(FullString, 1), Left(FullString, 1) & " ")
                    Case Is = 2 ' a_b_c
                        Burst = Left(FullString, 1)
                        For k = 2 To Len(FullString)
                            Burst = Burst & " " & Mid(FullString, k, 1)
                        Next k
                    Case Is = 3 ' ab_c
                        Burst = Left(FullString, Len(FullString) - 1) & " " & Right(FullString, 1)
                End Select
                Range("A1").Offset(i - 1, j) = Burst
                Debug.Print Burst,
            Next j
        End With
    Next i
End Sub

The debug.Print statements simulate the columns so that you don't have to keep switching from Worksheet to Code.





by Skilled (477 points)

Hi Yasser,

Exact parameters include the following:

  • You say you can define the maximum spaces. The absolute maximum would be helpful, because the combinations might increase beyond the point of a speedy VBA solution
  • Does the process (macro) need to handle different string lengths? I know you said the list will consist of strings of the same length. What I am asking is, will the list sometimes contain 3-letter strings and other times, 4-letter strings?
  • What is maximum string length the process will have to handle?
  • When a single list is processed, will the output be ONE of the possibilities for strings or, as you mentioned, ALL possibilities?
  • Does ALL possibilities mean for just one value for spaces or does it mean all values from one space to the defined maximum number of spaces?
  • Does the process have to check for invalid possibilities, such as a list of 3-letter strings combined with 3 or more spaces?

One parameter that you might want to clarify is the question you asked earlier:

Is there a way to make the spaces dynamic according to the string length?

I am not sure I covered them all. The point is, the more constraints you can describe, the better chance you can get the type of process you need.






by Expert (892 points)
Thanks a lot Mr. Mitch for your interest

* The maximum of spaces may be five spaces

* The length of the strings will be fixed (more than three in length and less than ten)

* The maximum string length will be 12

* All possibilties if possible


or another approach if possible to have a space only at the points of the strings that gives meaning for each word

example: say the string is "helloworld"

In this case I need just one space between hello and world

another example: "thenicecat"

Here's the possible output would be "the nicecat" & "the nice cat" & "thenice cat"
by Skilled (477 points)

Hi Yasser,

That's what I'm talking about. laughyes

Now, I can propose possible methods for you to consider, as you build this out.

Let's look at the last thing you wrote, because this seems like the real goal and is more challenging than just randomly separating strings...

If you have datasets (the strings that will go into column A) limited to 12 characters in length, it's quite possible to create a "search engine" to find meaningful words. I'm not really conversant with finite state machines, directed graphs and tries [link], so I'll leave that to you.

My approach, which may work with small datasets, is to create a dictionary of 3-letter words, 4-letter words and 5-letter words. You would pre-load this dictionary every time you wanted to process a list of words in column A.

Now a simple iteration would be required to loop through each string. For example, if the string is "thenicecat" you would compare the following substrings to your 3-letter word dictionary: THE, HEN, ENI, NIC, ICE, CEC, ECA and CAT

Depending on what you were hoping to accomplish, you could pause after each successful match and start a second search for another word. If you don't find a 3-letter word immediately following THE, you could check the 4-letter word dictionary and so on.

Obviously, the success of the splitting will depend on how comprehensive your dictionary is. But let's assume you have all the words. It won't take long for your procedure to detect THE, NICE and CAT. You can output the split string and move on to the next string in the list.

I hope this gives you some ideas for how to proceed.





by Expert (892 points)
Thank you very much. In fact I couldn't get what you mean with the idea of building dictionaries. I thought of using the Spell Checking feature existing in office but I can't get the start point till now.
by Skilled (477 points)


That's a cool idea! Unfortunately, according to the IntelliSense, you have no way to get anything useful from it:

Anyway, if the Dictionary object is too much of a hassle, you could just make an array of the 3-, 4- and 5-letter word lists. Just loop through them as mentioned earlier.






Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.