Free Microsoft Excel 2013 Quick Reference

Copy text from multiple cells into one cell Results

Can excel copy text from multiple cells into one cell?
if so can you explain how to do that.
thanks
kenny

Hello everyone! I've searched and searched for tutorials about this and cannot find anything that works.

I am trying to merge two columns into one column. I have selected the two columns (also tried as experiment to select just two cells in a row). Then when I click on the Merge and Center button, I get the following message: "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only." And it does!

I've tried to copy and paste the one cell into the other cell (which I've done many other times but it will not allow me to do so. I click on copy one cell, then highlight the other cell that I want to paste into then suddenly the paste function is not highlighted. I tried many times. Does not matter if I try to paste into the cell itself or the address bar area at the top. I do not want to replace the data in one cell with another, I want to add the data from one cell to merge the two cells together into one cell.

The data contains both text and numbers that I want to merge as follows:
(CELL 1) Size:Small:1:1:0:0:0::|Size:Medium:2:0:0:0:0::|Size:Large:3:0:0:0:0::|Size:XLarge:4:0:0:0:0::|

Here is (CELL 2) Color:Black/Pink:1:1:0:0:0::|Color:Pink:2:0:0:0:0::

These are attributes of products I'm trying to load into my ecommerce store. The way this shopping cart solution works is if you have multiple attributes such as above, (size and color choice), in order for it to show both choices to the customer, it must be all in one column. I did not know this at the time and made an additional column, one has the size attributes and the other has the color attributes.

What I am looking for is a way to merge the two columns into one single column and NOT lose anything out of either column. I can put the two data sets into the same column in either order. Does not matter if size or colors are first, I am just looking for a way to do this all at one time and not have to go in and re-type every one of them individually which is a huge hassle.

Thank you in advance for any help.

WolfLover

I have approx. 35 workbooks similar to the attached in a single directory.
Each workbook has 3 tabs named Help, Example and Template.
I need to copy the 36 Template tabs into one new workbook.
I would also like to rename each tab in the new workbook based on the text in cell A1 of the originating Template tab. Many thanks in advance!

I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.

What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.

Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?

Many thanks.

I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.

What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.

Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?

Many thanks.

Hi, I am doing some qualitative analysis of some text using excel. My data right now is in an html table, and when I try to copy the table it sometimes puts the text that was intially in one cell into multiple cells in Excel if there were breaks in the text within the cell of the table. So, if a cell contains 5 lines of text with line breaks between them <br>, when I bring it into excel it puts the content into 5 different cells (in the same column). Does anyone know if there is a way: 1. to prevent this from happening when I paste, or 2. to combine all 5 of the cells into one cell with multiple lines? Thanks for any help!

Good Afternoon

I am not familiar with using VBA and believe this could solve my problem, I would be glad of any assistance.

I am trying to copy text from sheet 1 of my workbook onto sheet two of my workbook, whilst consolidating into a nice neat list omitting any blank cells.

The text appears on multiple tables each table has 10 rows throughout worksheet 1

A1 = Good
A2 = Bad
" "
A10 = Fantastic

A15 = Good
A2 = Bad
A10 = Fantastic

I hope this makes sense, I do have a formula that will do this =IF(ROW()-ROW($E$500:$E$509)+1>ROWS($K$35:$K$44)-COUNTBLANK($K$35:$K$44),"",INDIRECT(ADDRESS(SMALL((IF($K$35:$K$44<>"",ROW($K$35:$K$44),ROW()+ROWS($K $35:$K$44))),ROW()-ROW($E$500:$E$509)+1),COLUMN($K$35:$K$44),4))) however I have multiple tables and the formula greatly reduces the speed at which the sheet is working.

Any help is very much appreciated

I have to transfer some date from PDF's to an Excel spreadsheet. Sometimes I want to grab several lines of text and put it in one cell. However, if I copy the following text, for example:

Multiple lines of text Multiple lines of text Multiple lines of text Multiple
lines of text Multiple lines of text Multiple lines of text Multiple
lines of text Multiple lines of text Multiple lines of text Multiple lines of text

and then try to paste into a cell in Excel, it pastes into three cells instead. Anyway to change this default behavior?

Problem. Im trying to convert a .Doc question bank into an excel spreadsheet that i can use for flash cards. The format i need the spreadsheet in is

A ............. B
Question....Answer

basically. Well what i have now is the imported word doc with all questions and answers all in consecutive rows. The catch is some of the answers are mulit lines A, B, C and or D all of the above (and i need to see all of the above) so to say
So it looks like this:
A
Which of the following are letters
A. A
B. 2
C. C
D. A and C

and I need it to look like

A....................................................B
which of the following are Letters........A. A
.......................................................C. C
.......................................................D. A & C

where all of that info is in Row 1.

So i need to be able to select multiple cells, copy the text and paste that text into 1 cell. I have tried the past special/values but that still puts the data into multiple rows. I dont think a formula will work because i will have to re-write it every time and thats just as much work as copying the text only and pasting it for each row.

I have 439 Questions to do this for and some are only one line where others are up to 4 lines in the ABCD format.

there surely is a simple way to copy text only ... any help would be great.

thanks all.

Hi,

Please help the new guy on the forum.
I have workbook that expands or shrinks in number of worksheets each time and I need to gather information from each worksheet to compile a "total" spreadsheet. The location of the cells to be copied in each worksheet vary however it is always in the same column.
Managed to find a macro that will collect the information if it is located in the same cell, across all worksheets but did not manage to find something that will conditionaly copy.

Need code, that will search based on text that will be found in the worksheets and then copy the values from the column next to it (same row) to the "total" worksheet. If any of the text that is to be searched in the worksheet does not exist, then it should leave the cell "blank" in the "total".

I have attached a workbook of with the example of the worksheets that I have and the result that I want to have at the end, in the "total" worksheet.

Any help is appreciated

Dear forum users,
I am importing multiple text files into one excel sheet, using a script I have attached bellow (also see here: http://www.ozgrid.com/forum/showthread.php?t=155837). The script imports each of the files in my directory into one column (the files are composed of 2 long tab-delimited columns, and the script imports only the first one). Since the columns in my text files are of different length, the resulting columns in the xls sheet do not have the same size. I currently need only the last cell in each column, and copying each last cell manually is rather time consuming. I was wondering if there is an easy way to adjust this script, so that it imports only the last cell/number from each imported text file).

Any help would be greatly appreciated

Martin


	VB:
	
 test() 
    Dim myDir As String, fn As String, ff As Integer, txt As String 
    Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer 
    myDir = "c:test" '

I will try to explain this best as I can. I have two separate Excel files. One is a basic spreadsheet with headers, the other is a custom form our client built. We need to take the information from our spreadsheet and populate our client's form.

I have attached samples of both files. The spreadsheet file is saved in a text format ... my spreadsheet filesize said it was too large.

The items on the spreadsheet are clothing. Some items will have identical vendor part # (VPN). We want to put all items with the same VPN & cost on the same worksheet. If the VPN or cost changes, we need to begin a new worksheet. For example, each group below would go on it's own worksheet:

VPN Cost
1234 $20
1234 $20
1234 $20

1234 $23
1234 $23

1235 $20

The top of the form contains the common information for that VPN. For example:

VPN: 1234 Description: Jeans Cost: $20

The bottom is just a list that would contain the item specific info as well, like sizes and UPC code. For example:

VPN: 1234 Description: Jeans Cost: $20 Size: 34x30 UPC: 012345678
VPN: 1234 Description: Jeans Cost: $20 Size: 34x32 UPC: 012345679

This spreadsheet contains over 3000 lines, so we would REALLY like to automate the process of putting it in their form if possible! So much room for error if we do it manually, not to mention the time it would take.

I have messed with this non-stop the past few days, so if I haven't explained clearly enough, please let me know!!

Thank you in advance for looking at this!
Mary

Hello all,

Okay here is my situation. I've tried all I know so I figure its time to throw it up to the excel gods and see what you all can do.

I have a workbook that has two tabs of raw data (One is called "Open" the other "Filled". The sheets include a variety of information only some of which is relevant towards my current cause.

The fields on both sheets match in layout and title. I need to search through both sheets data and display summary information on a third sheet.

The fields I need to search are "Date Signed", "Manager's Name", "Person being replaced"

I need to search row by row and evaluate the following.

If the "Data Signed" column value falls into one of four date ranges for the year 2006 (1/1/06 - 4/1/06, 4/1/06 - 7/1/06, 7/1/06 - 10/1/06, 10/1/06 - 1/1/07). However in this column are some blank cells and some non-date information (headers for subsections which are text). Additionally, the information is not and cannot be sorted.

Then I need to search inside of the results from the "Date Signed" search to see if the value in "Manager's Name" match the specific manager I'm searching for.

Upon that search being complete, I need to search within those results to see if a value exists in the "Person being replaced" field. If values exist, I need to count how many and return the result to a summary sheet.

Take a look at the example spreadsheet I made. It shows the raw data and shows you how I intend to display the summary results.

I’d love if this could be done with functions excel already has – I’d prefer to be able to copy and paste the formula into the cells. However a macro would be good too. A pivot chart is not as preferable – but if that’s what works that’s fine with me.

In essence, I have been stumped by this so any ideas of solutions will be much appreciated. Thanks and I look forward to seeing this great community find this solution.

Hello All!
Let me start by saying "Thank You" to all who have helped me out in the past ... I've been searching the forum for a while, but I don't think I have the knowledge (yet ...) to adapt what I have found in some strings to what I specifically need.

I am trying to copy partial data from Column A into Columns B and C, then I want to delete Column A without affecting the results in Column B and C. Column A consists of a stock number in the format "1234-56-789-0000" (including the dashes) and is formatted as TEXT.

I'd like to paste the "1234" from Column A into Column B and the "56-789-0000" from Column A into Column C AND remove the dashes (-). I need to do this for the range (or number of rows) that is populated in Column A. This could be as many as 50,000+ rows of data. After this is done, I want to delete Column A.

I tried using the LEFT and RIGHT formulas, but they are dependent on keeping Column A intact. This is what I'm trying to make it look like (I hope this looks OK in the post ... please disregard the "."):

........... A .................. B .............. C ......
1234-56-789-0000 ..... 1234 ..... 567890000

and then eventually like this:

. A ............. B ......
1234 .... 567890000

On occasion, leading zeros in Column C have disappeared, and it's necessary it stay in a 9-digit format. There are 2 additional columns of data, but they shouldn't be affected by the above.

I sincerely appreciate any help anyone can offer!

George

I have a challenge that I have been unable to solve.

I am trying to set up a spreadsheet to function as a data base for a project that I am doing. I need to be able to enter data into a cell and automatically update other targeted cells with the same data including font formatting. (Using a Formula, "=" ,works perfectly with the exception of Font formatting attributes).

Here is what I currently do:

I must "Copy" a cell and "Paste Special", (all formats) and the data is duplicated correctly into the targeted cell. This is a manual task that is labor intensive, time comsuming and prone to errors. I need to automate this process if possible.

Perhaps setting up a Macro that captures a "Parent" cell and copies the entire contents to multiple "targeted" cells is a solution. One of the challenges would be that the "targeted cells may move because columns or rows have been added/subtracted. (This is the concept of reusable data, get the data right in one place and all of the rest is corrected automatically. In a typical worksheet I may have 20-30 different cells that need to be duplicated to vasious locations).

It seems like this is a solvable problem but it's beyond my skill level. I would appreciate help from someone with the necessary skills.

Thanks in advance for your help,

Doc

Hello all,

I am sure this is a well worn subject but frustratingly I can't find the correct info via search which is relevant to my plight......

I have a spreadsheet which is updated weekly, each week is input on a fresh page of same workbook. The sheet is a straightforward cashflow which has had the actuals inputted, thus a true week by week closing bank balance has been established over many weeks.

I basically would like a summary page which pulls in each weeks closing balance and then I can graphically demonstrate the weekly movements, I figured simplest way would be to copy and paste value each weeks line into a fresh sheet but insert a leading column with the week I copied data from (this date is shown at the top of each page so no computation is required). One point to note, it's not a dead cert that the closing balance is always on say, row 53, however the text would always be say, "closing balance"....

I bow to everyones far superior knowledge and ask a simple way this can be done via VBA, would also be interested if there is an excel formulae which can drill down into several selected sheets without having to point, ie. say sum of A1 on sheet 1 through to a1 on sheet 2, .......a1 on sheet53?

Many thanks in anticipation.
Gnip

I sell merchandise on ebay, and have been looking for a faster way t
list. What I do is take and Excel Data Source, and run a mail merge o
the html code i post on Ebay (via a 3rd party listing company). So, i
I have 50 data entries in Excel, I will generate the code utilizin
Mail Merge 50 times. This was the easy part.

My Problem is, that I am trying to take each instance of my html code
and trying to copy or import it into 1 Excel Cell. As you all ar
probably aware, utilizing the import utility will span across multipl
cells.

I would like to take my data, set some type of delimeter, and impor
all of my code into one cell, so I can upload easily and quickly.
would rather work on this for a few days, then list a bunch of item
slowly. There has to be a way of doing this. I am somewhat technical
but all I can really do in Excel is a couple of simple calculations.
Definitely not an expert! I have a sample text file, with 2 instance
of the HTML code. Help!! Best Regards, Natasha Cook

+----------------------------------------------------------------
| Attachment filename: formletter.txt
|Download attachment: http://www.excelforum.com/attachment.php?postid=376912
+----------------------------------------------------------------

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Hi folks,

I have about 150 Excel files containing 1 worksheet each. The files are created by posting an ASP (Active Server Page) into an excelfile and the files are all saved as .xml
All files have the same design -
All but one column contains regular text as data Column "H" contains a Combobox with 4 possible answeres "keep, revise, archive, remove".
the goal is to import all of those sheets programatically into one Masterfile - preferrably an Access Database.

I have the following problem that I do need help with:

The selected values in the Combobox dont copy into another spreadsheet (column H stays empty).
I came up with a Macro that will grab the selected value from the combobox and move it into the the underlying cell - but i don't really want to copy the macro in 150 files that are ready-to-go kind of thing.
So I am looking for a simpler way to combine the worksheets - any ideas?

if it is of any help this is what i came up with so far

	VB:
	
 Test1() 
    Dim o As Object, buf, c As Range, i As Long, str As Variant, pos As Integer, numObj As Integer, newCell As String 
    pos = 0 
    i = 2 
    On Error Resume Next 
    If Err.Number  0 Then Exit Sub 
    On Error Goto 0 
    For Each o In ActiveSheet.OLEObjects 
        numObj = numObj + 1 
        If TypeName(o.Object) = "HTMLSelect" Then 
            i = i + 1 
            buf = Split(o.Object.Values, ";") 
            str = Split(o.Object.Selected, ";") 
            For Each s In str 
                If s = "TRUE" Then 
                    newCell = buf(pos) 
                    Range("H" & i).Select 
                    ActiveCell.FormulaR1C1 = newCell 
                    MsgBox (buf(pos) & " - " & pos & " - " & s & numObj) 
                Else 
                End If 
                pos = pos + 1 
            Next 
            pos = 0 
        End If 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the code works fine and would be called upon opening the spreadsheet,
is there a way to do this from the new "masterfile" in the following manner:
cycle through all xls files in a given folder,
open each file (automatically)
copy all data after adding the selected value into the underlying cell
save and close the masterfile

Some sample or instructions would be highly appreciated - thank you
Thanks

I am creating a form that will be used to input data, in that form there are 6 regular text boxes and two multiple selection listboxes that i have already created and work perfectly except that only one selection shows up on the worksheet. I want the output to be comma delimited in the cell. Also I want the named ranges that are supplying my listboxes to be dynamic and update when new values are entered in my LookupLists Sheet.

The two listboxes are named: listCarlisle and listAction

Any help would be awesome, Thank you in advance!

Code:

Private Sub
ComboBox1_Change()

End Sub

Private Sub Action_Change()

End Sub

Private Sub cboCarlisle_Change()


End Sub

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ClauseData")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a company name
If Trim(Me.Company.Value) = "" Then
  Me.Company.SetFocus
  MsgBox "Please enter a company name."
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.Company.Value
ws.Cells(iRow, 2).Value = Me.Regulation.Value
ws.Cells(iRow, 3).Value = Me.ClauseCode.Value
ws.Cells(iRow, 4).Value = Me.Revision.Value
ws.Cells(iRow, 5).Value = Me.RevDate.Value
ws.Cells(iRow, 6).Value = Me.Description.Value
ws.Cells(iRow, 7).Value = Me.listCarlisle.List
ws.Cells(iRow, 8).Value = Me.listAction.List

'clear the data
Me.Company.Value = ""
Me.Regulation.Value = ""
Me.ClauseCode.Value = ""
Me.Revision.Value = ""
Me.RevDate.Value = ""
Me.Description.Value = ""
Me.listCarlisle.Value = ""
Me.listAction.Value = ""
Me.Company.SetFocus

End Sub

Private Sub cmdClose_Click()

End Sub

Private Sub Company_Change()

End Sub

Private Sub Label2_Click()

End Sub

Private Sub listAction_Click()

End Sub

Private Sub listCarlisle_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()

Dim cAct As Range
Dim cCar As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cCar In ws.Range("Carlisle")
  With Me.listCarlisle
    .AddItem cCar.Value
  End With
Next cCar

For Each cAct In ws.Range("Action")
  With Me.listAction
    .AddItem cAct.Value
  End With
Next cAct

Me.listCarlisle.SetFocus
Me.listAction.SetFocus

End Sub


-Sorry if this has been covered, I've been all over this forum & Google reading and I can't find this particular scenario. If what I am trying to accomplish has been explained in several steps I can definitely follow a few unrelated guides and piece them together.

Scenario:
1 Spreadsheet with entire building roster
1 Spreadsheet with list of people in building who have completed a training.

The Roster sheet has the names listed as "Doe, John"
The Training sheet has the names listed in multiple Cells: Doe John

I copied the data from each sheet into one file, and then used Text to Columns from the Data menu (with a comma delimiter) to separate the Roster names into different cells. The Training columns are significantly shorter than the Roster columns, and John Doe in the Roster column does not appear next to John Doe in the Training column.

So I now effectively have a total of 4 columns to work with, and around 500 different people in the full Roster (all sorted A-Z by last name). I would like to highlight a person's name if their name appears in both sets of columns one color, and a different color if their name does not appear in the Training column and only appears in the Roster column. If this can me more easily accomplished with the names in the "Doe, John" format I can recombine all the names.

If sample code is needed I have an example ready.

Thanks