joining text from multiple cells to one cell

[sethf00]

I need to export data from a database I am working with, to an exce
file, to make it easier for me to edit certain changes.

For one part of this, all of the data appears in one cell (seperated b
delimeters.)

I use the "text to columns" function, to change the data into multipl
columns to make it much easier to read, work with, and edit.

My problem, is changing it back to the one cell, so that I may the
import it back to my database.

Part of my excel sheet looks like this after the text to column:

2325 test

where the large blank space is two empty cells. I know the correct wa
to change this back to one cell, is to use a cell and type
=A1&"_"@A2
as an example. this works perfectly. however, i need to do this t
about 50 different cells. and it is too time consuming to type thi
out. is there a way i can select a whole bunch of things...like
selecting a whole column for a sum, or other functions. I know there i
the "Concatenate" function, but I can not seem to do that and highligh
the data I want. I'd still need to type it all.

Any suggestions??

 

[Jim Rech]

This is a utility macro I have that does the opposition of Text to Columns
on the selection. It puts a space between the entries in each cell across a
row. You might want to substitute something else (an underscore?).

Sub ColumnsToText()
Dim CurrRow As Range, CurrCell As Range
Dim NewText As String
Application.ScreenUpdating = False
On Error GoTo NextRow
For Each CurrRow In Selection.Rows
For Each CurrCell In CurrRow.SpecialCells(xlConstants)
NewText = NewText & " " & CurrCell.Value
CurrCell.ClearContents
Next
CurrRow.Cells(1).Value = NewText
NewText = ""
NextRow:
Next
End Sub

--
Jim Rech
Excel MVP
|I need to export data from a database I am working with, to an excel
| file, to make it easier for me to edit certain changes.
|
| For one part of this, all of the data appears in one cell (seperated by
| delimeters.)
|
| I use the "text to columns" function, to change the data into multiple
| columns to make it much easier to read, work with, and edit.
|
| My problem, is changing it back to the one cell, so that I may then
| import it back to my database.
|
| Part of my excel sheet looks like this after the text to column:
|
| 2325 test
|
| where the large blank space is two empty cells. I know the correct way
| to change this back to one cell, is to use a cell and type
| =A1&"_"@A2
| as an example. this works perfectly. however, i need to do this to
| about 50 different cells. and it is too time consuming to type this
| out. is there a way i can select a whole bunch of things...like
| selecting a whole column for a sum, or other functions. I know there is
| the "Concatenate" function, but I can not seem to do that and highlight
| the data I want. I'd still need to type it all.
|
| Any suggestions???
|
|
| ---
| Message posted
|

 

[Debra Dalgleish]

You could create a User Defined Function to do this. For example:

'===========================
Function JoinText(rng As Range)
Dim c As Range

For Each c In rng
JoinText = JoinText & c.Value & "_"
Next c

JoinText = Left(JoinText, Len(JoinText) - 1)

End Function
'================================

Then, on the worksheet, enter the formula with a reference to the cells
you want to concatenate: =JoinText(B2:F2)