Pages

▼

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


at

3 comments:

  1. Anonymous8:21 AM

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

    ReplyDelete
  2. Yasser Khalil2:37 PM

    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