cell's literal value

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Using Excel97

I have a data set downloaded from another program. It has
several
columns filled with text values. I would like to combine
this text
strings into one column.


What is the best way to do this?

Is there a way I can refer to the cell's literal value
rather than as
a reference to the cell and save the literal value into a
new cell?

Thanks in advance.

Andy
 
Hi Andy

=A1&B1&C1
or with a space between them =A1&" "&B1&" "&C1


You can use a function also

' =Rangecat(A1:A10," ") This in the worksheet
' from J.E. McGimpsey
' It is working for rows an columns

Public Function RangeCat(rng As Excel.Range, _
Optional delimiter As String = "", _
Optional direction As Integer = 1) As Variant
Dim myColumn As Range
Dim cell As Range
If direction = 1 Then 'by rows
For Each cell In rng
RangeCat = RangeCat & delimiter & cell.Text
Next cell
ElseIf direction = 2 Then 'by cols
For Each myColumn In rng.Columns
For Each cell In myColumn.Cells
RangeCat = RangeCat & delimiter & cell.Text
Next cell
Next myColumn
Else
RangeCat = CVErr(xlErrNA)
Exit Function
End If
RangeCat = Mid(RangeCat, 1 + Len(delimiter))
End Function
 
Andy, how about something like this =A1&B1&C1

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
To combine test into one cell use: A1&B1&C1 etc
To convert the results to values use Edit followed by Paste Special/Values
Select all the cell with formulas, click the Copy tool, immediately open the
Edit menu, locate PasteSpecial and check the Value box.

Bernard
 
Back
Top