This should be easy, I have a column of First Names, a column of second names and I wish to create a new column with First Name , space, Last name.
I can create a cell specific code such as
= CONCATENATE(B2," ",C2)
However I want to make this into a macro to run on any file that I open for every entry in the columns (I export a file weekly and need to run it each time).
My macro and VBA skills are very basic!
I have more than 30 Columns. I want to concatenate all these columns and put the value in one cell. When I do it, Exccel gives me an error, "You have entered too many arguments for this function"
View 4 Replies View RelatedThis is the concatenation macro I have for 2 columns. I want to make this more general in that I would like the program to insert a new column to the right and concatenate the 2 columns based on the current cell the cursor is on and the one adjacent to the left (-1). The loop works fine, the modification I need is for the following three (3) lines.
View 2 Replies View Relatedmy problem is that i have a huge number of rows and columns, what i want is to concatenate the data in all the columns to the data in the first one, but separately, this is really complicated, so let us assume that i have the following table
-----A-----B-----C-----D-----E
1 ---98---13----17----19----44
2 ---94---18----20----23
3 ---19---22----245---45----62
so after concatenating the value in A to all the values in the other columns and put the result in a new column we get the following output
-----A
1---- 9813
2---- 9817
3---- 9819
4---- 9844
5---- 9418
6---- 9420
7---- 9423
8---- 1922
9---- 19245
10--- 1945
11--- 1962
also we need to take in consideration the empty cells as they must be skipped during the process, also find attached my Excel sheet that needs the modification
I receive a report which, amongst the other columns of data, has 7 specific columns with four digit codes on each row. I would like to find a way to concatenate these 7 columns into Column A, inserting a - between each code and adding -uu at the end of the last (7th) code.
I wouldn't be requesting assistance at all with this if the codes were always in the same column. Sometimes, they move 1 or 2 columns left or right depending on the additional (unwanted) data in the columns in-between. The column names, however, never change: Code 1, Code 2, Code 3, Code 4, Code 5, Code 6, Code 7
I was wondering if there was a way to still concatenate these columns into Column A but also take into account that the columns shift...
I have many workbooks where Cols B and C and G and H all need to be trimmed of extra spaces, then concatenate B and C into Col D with a space between, same for G and H into col I. (If you hadn't guessed, these are first name and last name columns which need to be combined into one name.)
I have been doing this manually, inserting extra columns, copy paste special values, then concatenate, copy paste special values?
I would like code for concatenating the text in two cells (F2) and (F4) saparated by a gap
View 2 Replies View RelatedI've got a workbook with several sheets, Sheet_1, Sheet_2, Sheet_3 and so on.
Each worksheet has various estimates in them and cell B61 contains a sum
I also have a summary worksheet that contains the sums from each worksheet like so:
Sheet_Name
Sum
Sheet_1
Sheet_1!B61
Sheet_2
Sheet_2!B61
Sheet_3
Sheet_3!B61
In the sum column, isntead of having to manually type in Sheet_1, Sheet_2 and Sheet_3, I'd like to use the value listed in the Sheet_Name column
I've tried concatenating the value in the sheet_name column with !B61 but instead it uses the text value Sheet_1 and doesn't go to the actual sheet.
Is there a clever formula I can try in the sum column?
I need to concatenate some standard text with some cell references but I can't use quotation marks as I would in the normal excel function as it produces an "expected end of statement" error message.
Is there a way around this?
What I want to say is:
Range(B23).FormulaR1C1 = "CONCATENATE(With , Info!C18), entries, your expected rate is: , H21)"
I have a cell with some text in it that has formatting applied (bold in this case). I want to append some text on the end using VBA, but preserve the existing font formatting information. So say I have "one bold one" in my cell, I'm executing the following: ActiveCell.Value = ActiveCell.Value & "two"
But this wipes out the bold information. Is there any way of preserving the bold that's already in the cell. Either perhaps by copying the Characters object and reapplying it, or by some other method?
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))
I have a sheet that is going to list usernames in a range of cells (e13:e19). Who gets listed there depends on another condition. So, all the cells could have a name, or only a couple of them could have a name. In another cell I have a formula that is to concatenate the results of who is in e13:e19 range, each name to have a comma between. The problem results when not all of the cells have a name .. the commas still appear ... so results could be ted, mary, bill,,,,bob, jeff ..etc.
I am using the formula below but it still allows the non-needed commas to appear:
=MID(IF(ISTEXT(E13),","&E13,"") & IF(ISTEXT(E14),","&E14,"") & IF(ISTEXT(E15),","&E15,"") & IF(ISTEXT(E16),","&E16,"") & IF(ISTEXT(E17),","&E17,"") & IF(ISTEXT(E18),","&E18,"") & IF(ISTEXT(E19),","&E19,""),2,2000)
For a visual aid I am using a msgbox to view my results. I have "Scenerio 1" and 2.
Snippet of code (CommandButton1):
I have a list of about 1,500 email addresses. To send an email I need to have a comma between each entry. I know I can use the formula =(A1&,",",&A2) to concatenate cells A1 and 2, with a comma between the 2 strings, but is there a formula to automatication do it for A1 - A1500???
Or is there an entirely different approach I should consider?
I have a table where a field is called "Expected conversions at [X%]".
X% is a monthly calculation.
So to populate the table row name I tried ="Expected Conversions At"&" "&C28 where C28 refers to a percentage calculated elsewhere in the sheet.
Cell C28 is formated to 1 decimal place. But when I refer to it in this manner it displays to many decimal places.
The result:
Expected Conversions At X.XXXXXXXXXXX
It should say:
Expected Conversions At X.X%
How do I do this so as the % is dynamic and auto updates each month?
I've got a spreadsheet that is being somewhat auto-generated from another program that extracts four different lines of text and puts them into separate cells (Starting at W30, X30, Y30 AND Z30). I believe there are 15 possible combinations. What I would like to end up with is each cell concatenated with " - " separating each one, but ONLY if there is text there to separate.
The function code I have so far gets me very close to what I want, but there are a couple of small issues remaining. See Red Bolded Text.
Here's what I've got so far.
Code: ....
I need to combine data from two adjacent columns into one in a condensed version of a spreadsheet. As the spreadsheet itself is quite big (over 20,000 rows) I was wondering if there was a quick and efficient way to doing this.
Here is a sample of what I'm working on (I know this would be better off as an image but unfortunately Photobucket is blocked at work):
Contract: Apr-2007 4000 Calls
TimeStampDeliveryDateStrike
18-Apr-07Apr-074000
19-Apr-07Apr-074000
20-Apr-07Apr-074000
Contract: Apr-2007 4000 Puts
TimeStampDeliveryDateStrike
18-Apr-07Apr-074000
19-Apr-07Apr-074000..............
I'm trying to create a model for woodworking where I can enter fractional wood sizes and have Excel create panel sizes and board lengths for cabinetry. I can easily get the inputs formatted in fractions, but then I'd like to have a concatenation formula that joins two fractions and displays a panel size as a fraction. However, when I try to do this, the concatenation formula changes the fractions to decimals.
Example: cell A1 is 3/4" formatted as fraction. Cell A2 is 1 1/2" formatted as fraction. Let's say I want a to concatenate so that A3 reads
3/4 x 1 1/2
But when I use concatenate (A1," x ",A2) the answer in A3 reads
.75 x 1.5
for i = 2 to i = 23
c.Offset(0, i).Value = combo_& i + 1 & .Value
next i
I have 1 work book with 2 work sheets. I need the data from 1 cell on the first sheet to be merged with a cell on the second sheet, ( I could also do with this having a line being inserted) can this be done in the same cell or does the merge command only work into a new cell?
View 3 Replies View RelatedI have approx 1000 rows in my spreadsheet. Each row contains one column which is a number. What I would like to do is list all these numbers side by side with only a comma seperating them. Just say the first 3 rows have the following numbers 10003, 10056, 100039. I would like to get the numbers to appear in one cell like this: 10003,10056,100039.
View 3 Replies View RelatedSay I wanted to concatenate a row that had 10,000 values in it. How could this be achieved without having to click 10,000 cells while typing a comma in between each one?
View 5 Replies View RelatedPlease refer to the attached screenshot of my working spreadsheet.
I'm attempting to concatenate lines 86 and 87, month and year together.
The formula I use is: "=C86 & C87" - but I get a large number instead of "October 2009"
I would like to concatenate cells from a column based on the match in the
preceding column with a defined value.
Example:
Col. A B
Row1: T1 Jim
Row2: T3 Paul
Row3: T1 Joe
Row4: T2 Mike
Row5: T3 Caroline
The formula should return for T1 for instance: Jim;Joe;
(for T2: Mike;
for T3: Paul;Caroline
Cells in column A can repeat any number of times.
I have a column of numbers going down the sheet in column A.
I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.
my requirement is to create db2 query with the existing excel sheet data.
Cells in the excel sheet contains special characters like { ) , ( , , ; , , , _ , ... }
These cells has to be merged, is there any function in excel to do that?
Concatenate function is not working..! getting data error
Column A has a list of items beginning in A2:
1
4
11
29
109
234
Column B has another list of items beginning in B2:
CAR
BOAT
TRUCK
Would like to put a list in column D, starting at D1, that concatenates both lists like this:
1CAR
1BOAT
1TRUCK
4CAR
4BOAT
4TRUCK
11CAR
11BOAT
11TRUCK
etc until all combinations have been covered.
Notes:
-Both lists can vary in number of items
-Both lists will never have a blank cell until the end of the list
I am thinking of a looping macro to do this, perhaps a loop within a loop? But I am stumped how to go about this.
I know this should be done in Access. but I need to do it in Excel.
how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.
I'm using Win 8.1, Office 2013.
concatenating a variable range of data. Attached are examples of what I have and what the desired outcome is.
View 2 Replies View RelatedI have two problems with my PivotTable: 1. My PivotTable consists of a constant number columns and varying number rows, eg.......
The dates (with data) should continue to update as I add new date records to the source datasheet, but they don't. I try the refresh option for the table, but it only updates for the dates that it was designed for. I also have automatic updates set, but so far nothing is working. I have to rebuild the PivotTable each day after I add new data...
2. Second problem are the date labels on the rows. I need to have them include the literal day of the week such as
Thursday 01/01/09
Friday 01/02/09
Another less urgent problem that I would like to solve is that I have included additional percent calculations for the total column that I am having to rebuild each time. Can that be included in the PivotTable as well?
