joining text from multiple cells to one cell

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??

--
Message posted from http://www.ExcelForum.com

0
6/2/2004 6:17:47 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
578 Views

Similar Articles

[PageSpeed] 14

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
"sethf00 >" <<sethf00.177mjl@excelforum-nospam.com> wrote in message 
news:sethf00.177mjl@excelforum-nospam.com...
|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 from http://www.ExcelForum.com/
| 


0
jrrech (1933)
6/2/2004 11:00:46 AM
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)

sethf00 < wrote:
> 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 from http://www.ExcelForum.com/
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
6/2/2004 11:03:18 AM
Reply:

Similar Artilces:

Multiple Ranges for a Chart
I am trying to use ranges from several pages in one chart. When I set the source data to: =Sheet1!$A$2:$O$2+Sheet2!$A$3:$K$3 I get the following error: Reference is not valid HELP, what am I doing wrong? Are you trying to use ranges from multiple sheets for the same X or Y series? I've never seen that done. You can use X axis ranges from one sheet and Y axis ranges from another. "Vic" wrote: > I am trying to use ranges from several pages in one chart. When I set the > source data to: =Sheet1!$A$2:$O$2+Sheet2!$A$3:$K$3 > > I get the following error:...

Printing Multiple worksheets to a single pdf
When I select multiple worksheets to print to a single pdf, I always get mulple pdf files. For example, I select three worksheets I will get the first two in one pdf and then when the third is to be printed I receive another save dialogue box. I have tried several different PDF converter applications (Win2PDF, Adobe acrobat, etc.) and it occurs in all Any suggestions I'm not sure why this is occuring as I only use distiller occasionall but have you tried the old fashioned way of making a pdf?- print th document using your normal printer but check the "print to file" box This ...

Doing joIn with REGEXP? (MySQL)
Hi Im working with MySQL. I have similar data in 2 different schemas Im trying to relate. This particular example relates to US states being stored differently in both systems. So, say I have the 2 tables as follows: STATE_1 ----------------- | ID | STATE | ----------------- | 1 | AL | | 2 | AK | | 3 | AS | ................. STATE_2 ------------ | STATE | ------------ | US-AL | | US-AS | | US-AK | ............ I want to get the STATE_1.ID value where the STATE fields are equal. Note that STATE_2.STATE values are the same as those in ...

plain text #5
Can I set Outlook to "read" "all" new messages in plain text, instead of HTML. I know there is a feature like this in outlook express but I need one in Microsoft Outlook. Jacob On Wed, 8 Sep 2004 12:42:15 -0400, "Jacob" <info@kohn.ca> wrote: >Can I set Outlook to "read" "all" new messages in plain text, instead of >HTML. I know there is a feature like this in outlook express but I need one >in Microsoft Outlook. > What version of Outlook? -- <<<SgtRich>>> Email Client: Microsoft Office Outlook 2003 ...

better to filter on join or where clause
If you have something in your where clause that could be put in the inner join would it be better there? SELECT * FROM tableA a JOIN tableB b on b.key = a.key Where b.status <> 15 and b.status <> 20 or SELECT * FROM tableA a JOIN tableB b on b.key = a.key and b.status not in (15,20) Any difference? Thanks, Tom There is no difference when using inner join. The optimizer will generate the same execution plan. It is more a preference/style of writing queries. I prefer to place filters in WHERE and leave only join predicates in the ON clause: SELECT ...

Text Effects
Dear, How can I get a text effects-animation(flashing) on some of the cells in Excel. (Like MSword used to have text effects-animation) pls help Regards APK ...

Outlook attachments and text error
Where do I go to check and correct a problem with the attachment. I cant attach a file. Also others can read my text in the email I send, but I cant read emails from others. Why? What do I need to do ? 1. Post your Outlook version. 2. Tell us what you did just prior to this not working 3. Not assume that we all know what you are talking about - a few more details is better than posting just enough to get our curiosity going. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sen...

Text Boxes moving in document
Publisher text boxes move when a document is opened by another user on our network. How can I stop this? I fear this is a structural problem. See e.g. www.therapietips.nl. It's in Dutch but you will see a text box moving upon opening the browser. (You can check out the tool with which a site is made by looking at the code) In my own site I am working on now I am trying to use one text box and use images of other texts. I am also experimenting with saving Word documents as hypertext The problem I have is that I want a editor with which you can make quick examples of websites. ...

multiple copy one paste
As I use all Windows functions, I long for a way to do multiple copies, then one paste. I call it Copy Add. This would allow me to copy from diverse documents and paste to one destination. As an example, I have an email with three attachments. I use the information as a running record of transactions I have sent to a customer. I open attachment one and copy a range, then go to my transaction file and paste. Back to email two, the same thing. Then to email three same thing. I want to be able to copy (Add) from one, copy(Add) from two, copy(Add) from three, and finally paste int...

WTF happened to printing spots a multiple angles
Microsoft must have retarded monkeys designing their software. I upgraded to Publisher 2002 because of the amount of clients I had that were sending my 02' files. I was completely floored when I found that I can't send multiple spot colors at different angles to my imagesetter. Why would Microsoft say they have the most "printer friendly" software and then remove multiple screen angles? It's not like they have never added a way to do this in any previous version, they actually removed it from this upgrade. It's not a bug of any kind, Microsoft stated that the only wa...

Adding the date when another cell is set to Bet.
I'm trying to figure out a way to get today's date entereind into Cell D(whatever) when F (whatever) is set to Bet. I don't want to use the TODAY() funcution because I don't what the date in that cell to change when the book is closed then opened again. Thanks Steve One way is to use an event macro. When you change the value in column D to BET, have it put the date in column F. Rightclick on the worksheet tab that should have this happen and select View Code. Paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count ...

joining domain
hi! Is it possible to have the users rejoin xp,win7 to win2003 AD with the computer object already exist in the AD? i know that this can be done through administrator group, but can this be done by the user without adding them into the domain admain grp or accoutn operator? what's the best practice and previlege that i shd give to the support team who only need to able to join the pc into the domain? Thanks. They have to be able to delete and add. If you are talking about doing this to one machine not a real big deal but if you want folks to do this all the time it c...

Cell value depends on Combobox selection
I'm trying to create a price list using combo boxes for the user t select the number of items for each product. For each product, the user has to choose the number of items via combo box. The combo box is being filled from cells K1:K6 (through th ListFillRange in the properties), the LinkedCell in order to hold th selection output is M1. The various prices are listed in cells L1:L6. What do I need to do in order to dynamically display the product pric in a specific cell depending on the selection from the combo box? Many thanks, Su -- Message posted from http://www.ExcelForum.com S...

Formatting on unprotected cells
I have protected a range of cells in a worksheet. Although it is possible to enter a formula in the unlocked (unprotected) cells on the same sheet, it is not possible to reformat those cells, e.g format them as % or change the number of decimals displayed. Is there any way to allow formatting on the unlocked cells? Thanks This kind of protection was added in xl2002. The tools|protect|protect sheet has a bunch more options. There are a couple ways around it. You could copy a cell that was formatted the way you want and then edit|paste special|formats If you're developing a tool ...

Joining worksheets / outer join
I've found some spreadsheet joining discussion threads, but I haven't found one which I think is applicable... Basically, I have 5 worksheets in an Excel file to be used as a mail merge. Each worksheet has lists of companies and the industries that they handle. Some companies have offices at different addresses, which should remain as seperate rows. Some companies handle more than one industry and are thus in more than one worksheet. Some of the companies have one business contact for all industries, some have seperate business contacts. I want one worksheet, which merges data from...

Line breaks and plain text in Outlook 2002
As has been documented, Microsoft removed the option to encode in Quoted Printable in Outlook 2002. Does somebody know why this was done? Outlook is quite a robust program with many uses. One of these is e-mailing. For the high price of Outlook, I would expect that the user should get many options and many customizable features (it amazes me that one cannot customize the head of the original message when quoted in a reply, for example). I would also expect from Outlook something very basic, and that is the ability to communicate without line breaks. It is quite ironic that MS removes quoted pr...

saving default value for a text box
I'm using a text box for notes (it's not associated with any tables). When user entered some text there I'd like that this text would appear on the next time when user would open this form. I'm trying to use the following: Private Sub PrintHeaderText_AfterUpdate() Dim new_text As String new_text = Me.PrintHeaderText Me.PrintHeaderText.DefaultValue = "'" & new_text & "'" End Sub .... and the following code: Private Sub CloseButton_Click() DoCmd.Close acForm, Me.Form.Name, acSaveYes End Sub But when I open the form again ...

Too many different cell formats #10
Dear All I have a quite big excel and i am trying to make some changes in format and i receive the message "Too many different cell format" Probably i have many different cell formats. From where i can set up the format in order to know what must be the format of the excel file? I need away to identify the format from all sheets in order to give to the all sheets as common format as i can. Any ideas? ...

Query Join
Hello, I need your assistance as I am a beginner with MS Access 2007. Please review the SQL and hope it makes sense. I am trying to capture all records from 2010 AOP even if there is none from SAP GL side. It seems to work with SAP GL, but not all of the AOP side. What am I missing? Not sure how to fix. SELECT [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST ELEMENT], [Category conversion].[Cost Element Name], Sum([SAP P4 GL].P4) AS SumOfP4, [2010 AOPII with Benefits].P4 FROM [2...

Separate Date from a Text String
The data that I have looks like this: [10/24/10 11:45PM] And I would like to just get the date like this: 10/24/10 Is there any way to do this in Excel with a formula? Thanks!! Sara hi assuming your text string is in A2,try this formula, =MID(A2,2,8) regards FSt1 "Sara" wrote: > The data that I have looks like this: > [10/24/10 11:45PM] > > And I would like to just get the date like this: > > 10/24/10 > > Is there any way to do this in Excel with a formula? > > Thanks!! > > Sara One way... A1 = [...

How do I show only part of a cell?
I have a lot of text in one cell. Is it possible for me to show only the first line, and then put a symbol or something that shows all the other text when the cursor touches it? Would using a comment give you what your looking for? Rgds, ScottO "Leslie" <Leslie@discussions.microsoft.com> wrote in message news:8902C125-2ED8-4FD0-B239-797A27DCE8D6@microsoft.com... | I have a lot of text in one cell. Is it possible for me to show only the | first line, and then put a symbol or something that shows all the other text | when the cursor touches it? Maybe... I put a button fr...

multiple data label formats
Is it possible from a single data range to create a chart (pie) wit data labels of both "show percent" and "show value" next to each other The wizard only lets you pick one and I've got a large number of thes to do so i'd rather not put it in manually. Any help is appreciated Thanks, To -- ob3ron0 ----------------------------------------------------------------------- ob3ron02's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1545 View this thread: http://www.excelforum.com/showthread.php?threadid=27275 Hi, You will need to use a he...

How do I trace which cell a #REF! error pointed to?
I maintain some spreadsheets containing formulae like this:- =IF(OR(AN135="",AN135="-"),"-",IF(RIGHT(AN135,2)="OR","-",LOOKUP(AN135,Crib!$A$1:$A$272,Crib!$B$1:$B$272))). The cells containing the formulae are locked, and protection is normally turned on. Most of the time, the spreadsheets work fine, but occasionally, the user reports a #REF! error, and when I take a look, sure enough, somehow one of these formulae has been replaced by a version that must (I guess) be pointing to a non-valid cell. The error version appears like this - =I...

Blinking text
Hello, Does anyone know how to make the text blink in Excel sheets? Thanks for your help! Aimee Hi though this is possible (search Google for 'blinking cell') I' strongly suggest not to do this: - all solutions involve the usage of the OnTime method in VBA and will slow down Excel - blinking cells have nothing to do with serious spreadsheets :-) -- Regards Frank Kabel Frankfurt, Germany Aimee wrote: > Hello, > Does anyone know how to make the text blink in Excel sheets? > Thanks for your help! > Aimee Use Conditional Formating instead. You can make the text B...

Counting consecutive cells
I have a spreadsheet of hours worked per day, for employees. I need to have an easy way (a function would be great), that can tell me if any worker has worked more than 6 days in a row. If an employee only works Mon - Fri, them there would be a value of 0. If an employee worked 5 days, Sat and Sun, and then another 5 days, the function would return 12 (the count of consecutive days worked, greater than 6 consecutive days). The reason for this is to keep a check on worker fatigue, and compliance with maximum working hours. Thanks for any help OM hi, can you describe or show the data dispositi...