Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012

    Unanswered: Splitting wrapped text

    I have a database that has multiple lines of text within a cell but there doesn't seem to be a character for the carriage return. I want to seperate the lines into unique columns, but to do this I need to replace the carriage return with a delimiter.

    I have attached a single line of the database in a zip file

    Can anyone help?
    Thanks in advance.

    Martin Smith
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 1

    I assume you only want to slip the cells with carriage return in?
    If so, this is not stikley speaking wrapped text, but never ming the semantic (although in certain cirumstances this is cruicial!

    I also don't know how you are populating the speadsheet, but it by VBA ans automation (it's the only way be in full control!), the the first sub below illustrate a way of determinig the Ascii charaters of the text, which revials the carriage return is asci 10 and 13.

    The second sub splits the text ito an array at the carriage return (Chr(10)), and then removes Chr(13), and prints each line to screen. You will not that there is an extra carriage return at the end which reurns a Null string in this code.
    At this point you can do what you like with the line of text!
    Sub GetCharacters()
        Dim i As Integer
        Dim strText As String
        strText = Range("K2")
        MsgBox strText
        For i = 1 To Len(strText)
            MsgBox Mid(strText, i, 1) & " = ascii character No. " & Asc(Mid(strText, i, 1))
        Next i
    End Sub
    Sub SpiltText()
        Dim i As Integer
        Dim strText As String
        Dim strNewText() As String
        strText = Range("K2")
        strNewText() = Split(strText, Chr(10))
        MsgBox UBound(strNewText)
        For i = 0 To UBound(strNewText)
            strNewText(i) = Replace(strNewText(i), Chr(13), "")
            MsgBox strNewText(i)
            If strNewText(i) = vbNullString Then MsgBox strNewText(i)
        Next i
    End Sub


  3. #3
    Join Date
    Feb 2012
    Hello Mike,

    Thank you for your reply, I only really need to separate column K (Contacts) and I want each line separated onto a new column. So if I could replace Char 10 and Char 13 with a semi-colon then I can use this as a delimiter.

    Is this possible. If I sent you the whole file (8000 records) could you do this for a small fee?


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts