CODEWITHSUNDEEP
excelstringvba
Sooner2
Sooner2

Reputation: 17

Adding to an existing String Value

So in a document I wish to convert a column of letters into a single cell. For example if the column contained the letters h,e,l,l,o then the program would put into a single cell "hello". I attempted to do this with the following code but it leaves the output cell blank

Dim t1 As String
Dim t2 As String

Do While Cells(i, "B").Value <> ""
t2 = Cells(i, "D").Value
t1 = Cells(i, "B").Value
Cells(i, "D").Value = t1 & t2
i = i + 1
Loop

Upvotes: 1

Views: 612

Answers (2)

ANAND AGRAWAL
ANAND AGRAWAL

Reputation: 58

There is no need to use VBA for this. Excel has built in function CONCATENATE().

Use it like this:

In the cell where you need the output enter the formula 

=CONCATENATE(Select cell 1, select cell2,select cell3)

So you can select all the cells which you want to join. Put a comma in between each cell.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Function MyConcat(rng as range)
    dim rngEach as Range
    For Each rngEach in rng            
        MyConcat = MyConcat & rngEach.Value      
    Next rngEach
End Function

Then in the cell put: =MyConcat(B1:B5)

enter image description here

Upvotes: 3

Related Questions