Split words using VBA in Excel cells

debugcn Published at Dev

Diddy

I would like to split a string with tokens separated ‘;’ into 2 list of values. For example I have the string: AXX1.CYY1;AXX2.CYY2;AXX3.CYY3;AXX4.CYY4 I would like list it to be separated into 2 lists, the first should contain values on the right of ‘.’ whereas the second should have values on the left Practically, the outcome should be:

AXX1;AXX2;AXX3;AXX4
CYY1;CYY2;CYY3;CYY4

So far I think my strategy is create a loop in a VBA function that splits the words. The single value is in turn split into 2 portions which are put in a list. Further to this how do you apply the code to excel cells. If there is any other method to accomplish it is welcome.

John Coleman

Your basic logic makes sense. It can be implemented like thus:

Function ChopandSplice(s As String) As Variant
    Dim chunks As Variant, first As Variant, second As Variant
    Dim temp As Variant
    Dim i As Long
    chunks = Split(s, ";")
    ReDim first(0 To UBound(chunks))
    ReDim second(0 To UBound(chunks))
    For i = 0 To UBound(chunks)
        temp = Split(chunks(i), ".")
        first(i) = temp(0)
        second(i) = temp(1)
    Next i
    ChopandSplice = Array(Join(first, ";"), Join(second, ";"))
End Function

Sub test()
    Dim v As Variant
    v = ChopandSplice("AXX1.CYY1;AXX2.CYY2;AXX3.CYY3;AXX4.CYY4")
    Range("A1") = v(0)
    Range("B1") = v(1)
End Sub

Running the test sub leads to:

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-08-9

Comments

0 comments

From Dev

Related Related

Article

Split words using VBA in Excel cells

Split words using VBA in Excel cells

How to split and restructure cells using excel VBA

Split cells by uppercase and lowercase words in excel

Split cells with formula in next row Excel vba

Excel VBA to split cell into multiple cells

Using Split function with Cells, Range, Rows.count, and xlup on excel-vba

Split last two words in a sentence into two different cells in MS Excel

Insert formula into cells using excel vba

Excel VBA select range using cells and xlDown

compare 2 cells in excel by using vba

calculate count of certain cells using vba in excel

Using AutoFill in Excel VBA for a range of cells

Excel VBA - Split a cell into 1000 pieces and copy them into different cells

Tagging Words Using VBA in MS Excel

how to split cells like Jan to Aug As Jan,Feb, Mar, Apr, Jun,Jul, Aug like this in excel using vba

MS Excel: How to insert words in cells using Find and Replace

How to automatically split cells in word table using VBA

excel vba Split funtion using " as delimiter

How to split words in Excel

Excel VBA- Split Cell Strings into individual cells and copy cells to new sheet

Category name in excel charts, using VBA without using any Cells

Split Cells by Line Break VBA

VBA - How do I compare text in Excel cells to see if same words are found, exclusing punctuation?

split cell on underlined words vba

Loading VBA JSON variable then referencing it in Cells using Excel Formulas

Clear the Excel cells format using VBA without clearing the NumberFormat

Error on adding two cells using Excel-VBA for Mac

VBA Excel: Copying across sheets using alphanumeric range vs cells

How to do standard deviation on desired ranged of cells using Excel VBA?

Excel 2007 using VBA write formula to merged cells