Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Question Unanswered: Concatenate a String into Columns

    Hi all,

    I have a form in which I want to convert a string into a series of single characters and paste each single charcter into progressive columns in the same row.

    So I have a string called DimString with a value of Christyxo

    and the code I have so far does this;

    Sheets("Sheet1").Range("D32").FormulaR1C1 = Left(DimString, 1)
    Sheets("Sheet1").Range("E32").FormulaR1C1 = Left(Right(DimString, 8), 1)
    Sheets("Sheet1").Range("F32").FormulaR1C1 = Left(Right(DimString, 7), 1)
    Sheets("Sheet1").Range("G32").FormulaR1C1 = Left(Right(DimString, 6), 1)
    Sheets("Sheet1").Range("H32").FormulaR1C1 = Left(Right(DimString, 5), 1)
    Sheets("Sheet1").Range("I32").FormulaR1C1 = Left(Right(DimString, 4), 1)
    Sheets("Sheet1").Range("J32").FormulaR1C1 = Left(Right(DimString, 3), 1)
    Sheets("Sheet1").Range("J32").FormulaR1C1 = Left(Right(DimString, 2), 1)
    Sheets("Sheet1").Range("K32").FormulaR1C1 = Right(DimString, 1)

    Easy enough.

    The problem is that this would only work if the string is 9 characters long.

    Is there anything I can do to tidy this up so that it works correctly for any string up to 30 characters in length?

    Thanks in advance of any help

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    There are more optimal ways to do this (byte arrays) but I think this should suffice for your purposes:

    Code:
    Sub Example()
        Dim DimString As String
        Dim lChar As Long
        Dim rngStart As Range
        
        DimString = "Christyxo"
        Set rngStart = Sheets("Sheet1").Range("D32")
        
        For lChar = 1 To Len(DimString)
            rngStart.Offset(lChar - 1).Value = Mid$(DimString, lChar, 1)
        Next lChar
        
    End Sub

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Thanks Colin,

    This almost does what I want, but it's moving downwards in the same column, where I need to move it along the row (D32, E32, F32, G32...)

    I've at least cut my code down using your mid$ suggestion! so I'm part of the way there!

  4. #4
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Ignore me.

    I've sorted it.

    Code:
    Sub Example()
        Dim DimString As String
        Dim lChar As Long
        Dim rngStart As Range
    
        
        DimString = "Christyxo"
        Set rngStart = Sheets("Sheet1").Range("D32")
        
        For lChar = 1 To Len(DimString)
    
        RangeStart.Offset(0, lChar - 1).FormulaR1C1 = Left(Mid$(DimString, lChar, Len(DimString)), 1)
    
        Next lChar
        
    End Sub
    It was the offset that was wrong. I needed the " 0, "

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Sorry, my oversight - you're right, I coded it to split the letters out down a column rather than across a row. The change you've made looks good to me.

Posting Permissions

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