CODEWITHSUNDEEP
stringvbaexcel
Uwe Ziegenhagen
Uwe Ziegenhagen

Reputation: 694

Splitting a semicolon-separated string in an Excel function

Is it possible to create an Excel-Function to split a semicolon-separated string to n adjacent cells?

I can create a function which does the opposite (concat the values of n cells via semicolon and put the value into a specific cell), the opposite does not seem to work. Can anyone shed a light on this?

Upvotes: 0

Views: 3002

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19087

Yes, it's feasible, but you need to create array function which requires to call it as you do with any array function using ctrl+shift+enter

Function code:

Function SemiColToCells(SemiColStrin As String)

    Dim tmpArr As Variant
        tmpArr = Split(SemiColStrin, ";")

    'Next line creates array function!
    SemiColToCells = tmpArr
End Function

Picture of Excel sheet presenting example with correct and incorrect way of calling the function:

enter image description here

Upvotes: 5

Related Questions