0 votes
in VBA by Expert (916 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 (489 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 (489 points)

p.s. It may be tempting to use Right, instead of Left, to get the third column. That only works if you can be sure that the rightmost character in column A does not appear earlier in the string.

If you have abc, you will get the correct ab c

if you have acc, you will get the incorrect a c c

It was easier to just define a different instruction for the third split :)

by Expert (916 points)
Thank you very much.

I tested to change the number to 4 as I have the string abcd

I got "a bcd" and "a b c d" and "abc d" and "abc d" >> so the last two results are the same

At the same time there are other possibilties such as "a bc d"
by Skilled (489 points)
Glad to be of some help, Yasser.

 Changing the NumberOfSplits has nothing to do with the string length.

You have to add the Case Is = 4 ... and a specific transformation. As written, the Value of Burst didn't change when the 4th iteration was tested. Since the SELECT statement didn't find a case for 4, it did nothing. :)




by Expert (916 points)
Thanks a lot Mitch .. Is there a wayto make the spaces dynamic according to the string length?
by Skilled (489 points)
Hi Yasser,

Can you give some examples?

Case 2 already leaves a space between each character, regardless of length.

For other patterns, you just need to come up with the right combination of string functions.

One thing occurred to me: depending on the scope of these Space Patterns, would it be useful to have a "library" of simple processes that can be combined with some additional logic to handle any number of letter / space combinations?

Imagine the case statements giving a "recipe" for how to build the string. Currently, the case statements build the string but, if you have more than a dozen, some of which are similar, it might be worthwhile to expand on the string building functionality of the test subroutine.



by Expert (916 points)
I don't know exactly .. All what I need to put all possibles of spaces in between characters so for example: "hello"

"h ello" - "he llo" - "hel lo" - "hell o" (these are the possibities for just one space"

"he l lo" - "he ll o" - hel l o" (these are the possibilties for two spaces)

and so on .. We can define the desired maximum spaces required
by Skilled (489 points)
Hi Yasser,

If you are wanting to output all possibilities, that's a different process from outputting an arbitrary number of patterns. At this point, you have to get away from rigid string processing and deal with patterns.

One method might be to set up constraints, say, from 1 space to 3 spaces. The number of columns output would depend on the length of the strings in Column A.

If you can see how the input is read, then how the output is produced, we can focus on the method to transform each string iteratively.

You might get better help if you know the exact parameters required.



by Expert (916 points)
What do you mean with exact parameters?
by Skilled (489 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 (916 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 (489 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 (916 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 (489 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
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

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