Combining cells

Using Excel 2002:

I will open a text file into excel.  This file has no formatting, but the
end result would be using two columns.

The text file for example has:
Music1 from site1 and site2 special 90876
Music100 extra from site1 87654
Music02 misc sites 10020

I want to put the numbers, 90876, 87654, 65409 in the same column "B" and
have the other text combined into one column "A".  When I use space
delimited I can move the numbers in the same column.  How do I combine the
rest of the text which are word for word in columns A,B,C,D etc into one
column "A"?

End result:
"Music1 from site1 and site2 special" in Column A, and "90876" would be in
column B.
"Music100 extra from site1" Col A, and "87654" in Col B.
"Music02 misc sites" Col A, and "10020" in Col B.

Hope I'm explaining this so it's understandable.

MC




0
none8133 (148)
10/8/2005 6:32:55 AM
excel 39879 articles. 2 followers. Follow

1 Replies
749 Views

Similar Articles

[PageSpeed] 45

Hi Mark

Try entering in another column, say column F
=A1&" "&B1&" "&C1&" "&D1 and copy down
Copy the whole of the range in column F, move to A1 and Paste Special>Values
This will overwrite what is in column A with your concatenated string of 
text.
Delete columns B,C and D

BEFORE YOU START, make a copy of your data - in case of any foul ups!!!

Regards

Roger Govier



MarkC wrote:

>Using Excel 2002:
>
>I will open a text file into excel.  This file has no formatting, but the
>end result would be using two columns.
>
>The text file for example has:
>Music1 from site1 and site2 special 90876
>Music100 extra from site1 87654
>Music02 misc sites 10020
>
>I want to put the numbers, 90876, 87654, 65409 in the same column "B" and
>have the other text combined into one column "A".  When I use space
>delimited I can move the numbers in the same column.  How do I combine the
>rest of the text which are word for word in columns A,B,C,D etc into one
>column "A"?
>
>End result:
>"Music1 from site1 and site2 special" in Column A, and "90876" would be in
>column B.
>"Music100 extra from site1" Col A, and "87654" in Col B.
>"Music02 misc sites" Col A, and "10020" in Col B.
>
>Hope I'm explaining this so it's understandable.
>
>MC
>
>
>
>
>  
>
0
roger1272 (620)
10/8/2005 6:46:36 AM
Reply:

Similar Artilces:

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

Excel 2003- Tabbing to a specific location in a cell
I have created a template and I want to be able to fill in parts of the template by just tabbing and the cursor to move to the next cell...and to a specific area in the cell...how do i set up my template to do this? THanks so much unlock the cells you want to tab to. leave the other cells protected. then protected the sheet. -- Gary Keramidas Excel 2003 "monty the magician" <monty the magician@discussions.microsoft.com> wrote in message news:FD3728D8-E730-407E-B58A-FED2C6D9EA8A@microsoft.com... >I have created a template and I want to be able to f...

how do i set up drop down menus to populate cells in excel?
Hi, I'm trying to build a database in excel, and to aide data inputting, i'm trying to figure out if you can use drop down menu's like in the form view of access. eg in access you list all the possble responses and add this to the drop down menu for selection to populate the field so you don't have to type it out again. I'm using excel because several people will need access to the file and not many use access. Is it possible to do this in excel??? You can use Data Validation to create dropdown lists. There are instructions in Excel's help, and here: h...

Merged Cell Character Limitation?
Is there a limitation to the number of characters that can be entered into a merged cell? I have a document which has a merged cell consisting of 12 rows and 9 coulmns (for text entry). I set the cell to word wrap. However the word wrapping ceases after roughly 1500 characters, and the text is no longer visible to the user in the cell (even though there appears to be room). If this is a limitation, is there a known work around? Hi there's a maximum of 1024 characters per cell. Though you can increase this by manually adding line breaks with ALT+ENTER -- Regards Frank Kabel Fra...

delete values in several cells without deleting the formulas
I wanted to clear the values in a spreadsheet without deleting the formulas or formatting in the spreadsheet. For example: A budget sheet made for every year, the formulas and formatting remains the same, only the values change. I tried paste special formulas....in this case, if values are present, they get copied too. Hi you can't delete the 'values' from a formula. The formula will always calculate its result -- Regards Frank Kabel Frankfurt, Germany "dranreb" <dranreb@discussions.microsoft.com> schrieb im Newsbeitrag news:43255A99-86BA-45A5-BB09-FCAEC002A09...

Set Cell Padding on word tables with merged cells
Dear Experts: Below macro sets certain padding values for the selected table. Regrettably it fails to work on tables with merged cells. Have you got any idea how to re-write the code so that tables featuring merged cells also get worked on? Help is much appreciated. Thank you very much in advance. Regards, Andreas Sub SetPadding Dim myCell As Cell Dim myRow As row Dim myTable As Table Set myTable = Selection.Tables(1) For Each myRow In myTable.rows For Each myCell In myRow.Cells myCell.TopPadding = CentimetersToPoints(0) myCell.BottomPadding = C...

How can I delete an infinite number of garbage cells to the right
I highlighted many entire rows with color. Now my excel wants to print 115 pages. I can define a narrow print area, but I really want to delete the garbage color cells to the right. When I drag to the right, there seems to be an infinite number of cells. hi don't drag. place your curser in the first row and column that you want to delete left. hold the shift key down with left hand and with right hand, hit end then the down arrrow. hit the end key again then the left arrow. excel will select all cells from row 1 to bottom and from curser position in row 1 to far left. ...

Printing Entire Contents of Cell
A couple columns in a spreadsheet have lengthy text with the cell formatting set to wrap. Not all of the text displays when viewing the screen, but when clicking on the cell to edit, the contents are there. The real probem is that when printing, the contents of some of these cells are truncated. How do I get the entire contents to print? -- waterdawg ------------------------------------------------------------------------ waterdawg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26835 View this thread: http://www.excelforum.com/showthread.php?threadid=40084...

Sorting and coloring cells, formula or macro help needed
I have a problem that i don't know how to solve. I wrote it all in my excel file that needs to be done. File is here http://www.sendspace.com/file/q4jc71 Anything that isn't clear, pls ask me. I need this really badly, because my tables sometimes have thousands of those records. HTH: http://excel.tips.net/Pages/T002581_Sorting_Data_Containing_Merged_Cells.html Micky "domyrat" wrote: > I have a problem that i don't know how to solve. I wrote it all in my excel > file that needs to be done. > > File is here http://www.sendspace.com/fi...

How can I copy a list from word into a single cell in excel
Data in word is presented as a bullet list. I have copied this data into a new doc and removed the formatting to leave just the list of items. I now wish to copy this data into excel but want it to appear in one single cell retaining its list format rather than continuous text Well, you can save it as TEXT and then read into WORD. If you really want it in one cell, you can then concatenate the values together using a formula similar to this =A1&B1 If you want a space between the data, use =A1&" "&B1 Remember that there is a cell length limit that you may come...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

Open Workbook to Specific Cell
I do not understand why Excel works this way, and would like help please to understand it, and how I can work around it. I am using Excel 2003. I have 2 workbooks. My test workbook (to understand how this is working) has a macro behind a button that calls... Workbooks.Open Filename:="E:\Address and Telephone Book.xls". Now this works fine except that when the "Address and Telephone Book.xls" is opened, the active cell happens to be the last active cell location from the last time the "Address and Telephone Book.xls" was saved. This is very an...

cell displays formula
Hello, I sometimes run into this phenomena where I change a formula or type in a new formula and suddenly Excel keeps displaying the formula instead of results. This usually happens with a few cells only, i.e. the majority of the cells just display regular results while only the few affected ones display the formulas. I am not in formula auditing mode, so I believe this is pretty much an Excel bug. But what can I do about it? Thanks, Peter -- pwermuth ------------------------------------------------------------------------ pwermuth's Profile: http://www.excelforum.com/member.p...

How to find multiple cells/replace whole cells w/data
I am trying to find a specific set of characters in multiple cells in a column. Once found, I need to replace the data within found cells with a specific text string. I have been able to replace a text string, but not the whole cell. Perform a search and replace, and add an asterisk before and after your "search for" parameter. Replace with your desired cell contents. Search for: *your text here* Replace with: desired cell contents Instead of 'Find', turn on data filters and select Custom... Use the drop-downs to make the condition 'contains' and then ente...

How to exclude cells from a datasource?
Hi, I have an XY chart in excel97 that uses a range of cells for the data source. Every now and then I would like to exclude a XYnode from the chart. Without having to reselt the datasource. Is there a value one can enter in the cell that exludes it from the graph (compare: putting text in a cell excludes it from the SUM() function). For example point X Y a 1 1 b 2 2 c 3 3 Gives a graph with two lines connecting the three points While point X Y a 1 1 b ? ? c 3 3 Should give a graph with one line connecting point a and c. Point b is left out. Or is the...

Incrementing Cell Numbers
All: I have created an excel "Workorder" form for our employees to write in what machine they are working on, what parts they used, and how long they spent on the repair... The problem... I need to print out hundreds of these forms for the men to use, and each copy of the form needs to have an unique number. Is there a way to program a cell to automatically increment by 1 every time it is printed? Again, I need to print hundreds at a time, and I need them to automatically increment by 1. TIA, Lance Place this in ThisWorkbook module: Private Sub Workbook_BeforePrint(Cancel ...

Changing worksheet cells from inside a function
Hi The following code in VB (excel 2000): ----------- start code function f(x as double) as double cells(1,1) = "A" f = x + 2 end function ----------- end code produces #ARG error when inserted into a worksheet. The offending line is: cells(1,1) = "A" Does it mean I can't change worksheet cells from inside a function ? But macros do it. What I'm doing wrong, or is there a way around ? Best greetings Antek, Warsaw, Poland -- remove SPAM before onet.pl antekL1@poczta.SPAMonet.pl Hi Antek, A function, when used in a worksheet, cannot change or alter an...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

Date value in cell with text
I would like a cell in Excel 2000 to have some text plus today's date, like this: "Total orders as of 3/09" I was able to pull the current date with =NOW() and right clicking the cell and choosing the date to look like 3/09 I was thinking that my formula should look like ="Total orders as of "&NOW() Any thoughts? ="Total orders as of "& TEXT(TODAY(),"m/dd") -- Kind regards, Niek Otten Microsoft MVP - Excel <tonyrulesyall@yahoo.com> wrote in message news:e37153e1-42c4-4dce-892f-ed1362033ec3@41g2000yqf.googlegroups.com... >I...

"Too many different cell formats" is preventing file from opening
An Excel 2000 workbook saved successfully but when attempting to open it again I receive the message, "Too many different cell formats." I have since learned that a maximum of 4000 format combinations are allowed in a workbook. I've also learned how to correct the problem. Unfortunately, Microsoft's instructions don't address how to get the file to open in order to carry out the corrective actions necessary. Any ideas about getting the file open? A recent post from Dave Peterson on this subject.......... Saved from a previous post: XL: Error Message: Too Ma...

Reference a cell value in a formula
I have the following formula: =SUMPRODUCT((MONTH(Summary!$F$7:$F$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!$E$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...

Help!! Excel question: how to link a cell to a group of cells
Hi, Good day! I have a worksheet: A commentA1 --------------- B commentB1 --------------- A commentA2 ---------------- C commentC1 --------------- B commentB2 .... etc and want to create another worksheet to link to the first to look like: A commentA1 commentA2 --------------------- B commentB1 commentB2 --------------------- C commentC1 Can this be done in Excel? Thanks, lc Hi Luican............. In Sheet 2, wherever you want the contents from "Sheet 1 Cell A1", put this formula =Sheet1!A1 In Sheet 2, wherever you want the contents from "She...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Linked cells #5
I have two spreadsheets. Spreadsheet One has a list of names. Spreadsheet Two is formatted to print the first four names on a form. I am trying to set things up so that I don't have to copy the name into the cells when I want to print the next form. I have linked each of the cells in Spreadsheet Two to a cells A1, A2 A3, and A4 in Spreadsheet One. I have tried this using both absolut addresses and relative addresses. What I want to do is delete cells A1 through A4 in Spreadsheet One thereby moving the names in cells A5 through A 9 into cells A1 throug A4, so that the four cells in...

copy spaced cells to different spacing
On one tab I have formulae in every 4th row that I want to copy onto another tab where I want the same formulae every 2nd row. The rows in between these formulae are not blank. How do I best do this? Derrick You can use code like the following: Sub AAA() Dim Source As Range Dim Dest As Range Dim LastCell As Long Dim WS As Worksheet Set WS = Worksheets("Sheet1") '<<< source of data Set Dest = Worksheets("Sheet2").Range("A1") '<<< destination of copy With WS LastCell = .Cells(.Rows.Count, "A").End(xlUp).Row Set Sour...