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


3 comments:

  1. Anonymous8:21 AM

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

    ReplyDelete
    Replies
    1. Anonymous3:12 AM

      skip transpose

      Delete
  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

    ReplyDelete

StumbleUpon
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.