Concatenate a String into Columns

christyxo · #1 (**permalink**) 01-06-12, 06:34

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

Colin Legg · #2 (**permalink**) 01-06-12, 08:09

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

christyxo · #3 (**permalink**) 01-06-12, 08:41

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!

christyxo · #4 (**permalink**) 01-06-12, 08:54

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, "

Colin Legg · #5 (**permalink**) 01-06-12, 12:02

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech