If you need to split the string separated by delimiters as commas, colon, slash, dash or other delimiter quickly, let’s read the following article.
First, I will take a example of comma-separated string:
Topic: CPS1, CPS2, CPS3, CPS4
If only a few such strings are simple, we just need to split manual and finish the thought. But in the case of a lot of strings, that’s another story. To help you solve this problem, today I will share one way using VBA Excel.
When you want to split the comma-separated string in VBA, simply we use Split function. However, if only using Split function,we get only one result can be obtained at a time. To be as expected, you must insert a loop, starting from the first to the last character of the string.
Specifically, the code will be as follows:
Spliti = Range(“Sheet1!b1”).Value
'Explain the code: You put the topic string in cell B1.
Spliti = Split(Spliti, “,”)
For a = LBound(Spliti) To UBound(Spliti)
Range(“sheet1!A” & a + 2).Value = Spliti(a)
'Explain the code: You return the string result from cell A2 to A *, with * being the length the topic string plus 2.
After that, we create a button and asign the macro that we just wrote. So you can easily split the string separated by commas.
You can split the string separated by other delimiter by replacing comma with other delimiter.