Wednesday, February 29, 2012

How to Split Text in a Cell to Multiple Cells using Excel VBA

Convert a Text to Range using Excel VBA

The following snippet converts the Text to an Array by splitting using SemiColon delimiter and uses the Transpose Function to place it in the Range

Sub ConvertText2Range()

Dim sText As String, arText

sText = Range("c16").Value

arText = Split(sText, ";")

Range("D16:D" & CStr(16 + UBound(arText))).Value = WorksheetFunction.Transpose(arText)
End Sub


  1. Anonymous8:21 AM

    Could you post a modification so the result would show up horizontally instead of vertically?

    1. Anonymous3:12 AM

      skip transpose

  2. May be

    Sub ConvertTextToRange()
    Dim sText As String, ArrText
    sText = Range("C16").Value
    ArrText = Split(sText, ",")
    Range("D16").Resize(1, UBound(ArrText) + 1).Value = ArrText
    End Sub


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.