Excel "Text to column" - changing data back 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 ther
is the "Concatenate" function, but I can not seem to do that an
highlight the data I want. I'd still need to type it all.

Any suggestions???

:confused

 

[Earl Kiosterud]

Seth,

You may need to save the file as txt or csv (see the file types box in the
Save as dialog). This will separate your fields with commas, which most
data base programs expect anyway. This will probably be the best way.

If you're going to do it with formulas, copy the first formula down with the
fill handle (lower right corner). If you have commas or quotes in your
data, you'll likely run into trouble getting the data into the data base
properly.

 

[Gord Dibben]

seth

The easiest method may be to just make a copy of the worksheet before doing
your Text to Columns.

Do not alter the copied sheet data and use it to place back into the data
base.

If you want to use a user defined function that will concatenate cells,
ignoring blank cells, here is some code.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Assuming data is in A1 and D1, in E1 enter =ConCatRange(A11) and drag/copy
down column E.

When/if happy with the results, copy Column E and paste special(in
place)>values. Delete other columns.

Gord Dibben Excel MVP