I've searched quite a few times for Concatenating Multiple Columns and ones for Concatenating with Spaces.
I've found solutions that say use
=concatenate(A1, " ", B1)
or the VBA equivalent
I have at least 15-20 columns which I wish to concatenate.
Is there an easier way than to type for all 15 columns?
=concatenate(A1," ", B1," ", C1,....)
Also, the number of columns of each row vary
Say in L4 on form test i want to look for the value of A4 in Drill data range a2 to z1000 and retun a concatenated string of something like "sav 407 : 08-may to 11-may"
View 3 Replies View RelatedI wish to Concatenate contents of several columns into a separate column, i tried using the following "column1 & column2 & column3 etc..." however with this the blank cells get concatenated too...What should is use so that contents get concatenated but blank cells are ignored?
View 4 Replies View RelatedI am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9).
Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this :
=CONCATENATE(B4:B127)
How do I add spaces to a range?
My problem is very similar to the question asked in this thread [URL] .... but I am using numbers and would like to separate them with spaces.
I am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9).
Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this =CONCATENATE(B4:B127), how do I add spaces to a range?
my problem is very similar to the question asked in this thread http://www.excelforum.com/excel-work...ncatenate.html
but I am using numbers and would like to separate them with spaces.
I had wanted to go through my spreadsheet and concatenate two columns (A & B)into one (A) then delete the duplicate column (B), but have found no way to do that. Now I am trying to search then insert a column prior to the other two, concatenate the data into the new column then delete the columns. I am specifically having a problem with my Range statement and can't figure out how to activate it or discern it after using the Find command.
Sub GroupGender()
Cells.Find(What:="Group", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireColumn.Insert Shift:=xlToRight
With Range("a1", Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 0) = "=RC[1] & "" "" & RC[2]"
.Offset(0, 2) = .Offset(4, 2).Value
End With
Cells.Select
Selection.Replace What:="Group Sex", Replacement:="Grp/Sx", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select.......................
In Column A, I have several rows of data. Some cells contain numbers and some contain text and some are blank. In Column C, I only want the numbers in Column A. I do not want the text and I cannot have any gaps in the column.
So for example:
Column A: row 1: 456 row 2: 789 row 3: text row 4: text row 5: 398 row 6: text row 7: blank row 8: 124
in Column C I need:
row 1: 456 row 2: 789 row 3: 398 row 4: 124
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
I have two columns D and E that contain dates and values. Column D has the dates and Column E contains the corresponding values.
I need a macro to take all of the dates in column D (it is pulled from a different macro so the length of the two columns varies every time but starts at D2) and paste the first value in W1, the second value in AE1 , and so on (every eight cells).
I need to do the exact same thing with Column E except start at Y1, then AG1, etc (still every eight cells).
I will try to post an example later.
I have a lot of numbers in different columns:
A B C
1 1 2
2 4 6
3 6 9
4 8 .
5 9 .
6
7
8
9
.
.
.
I want to to order them so they leave blank rows if they don't match...like this:
A B C
1 1 2
2
3
4 4
5
6 6 6
7
8 8
9 9 9
.
.
.
I have the below set of data for example,
aaabbbcccdddeeeaaabbbaaabbbcccaaa
I need the output as,
aaabbbcccdddeeeaaabbbaaabbbcccaaa
As we know, a simple concatenate will work. But my columns are not limited and not easily countable. In my real scenario for some of the rows, data exist from column "A" till column "IB". So, I need to first identify the column data exist in each row and then to implement the concatenate function.
I'm trying to take the contents of 3 columns- A, B & C and have them form what I can use for a search string in column
D that I can use in a database I have.
Column A is first name
Column B is last name
Column C is Company Name
This is what i'm trying to get it to look like in column d:
("First Name Last Name" OR "FirstName.LastName") AND @companyname.com
writing a macro with following requirement
i need to display the third column with the concatinate values of column 1 and column 2
if A1 = Hi
B1 = All
then C1 should be Hi All
Item No amountinvoice no
2050377 14,870 105
5000085 120 1102
5000085 15,000 90197361
5000139 16,000 12346
5000237 31,000 56789
2050377 20,000 10111213
2050377 ($20,000)105
I have a spreadsheet with 3 columns. when I'm reimbursed money (see 2050377 for ($20,000) i'm trying to use VLOOKUP to search the spreadsheet and find out the invoice that relates to that reimbursement. but as you can see VLOOKUP finds the first incidence of 2050377 and gives me inv no 105 when it should be 10111213. my formula in C8 is =VLOOKUP (A8,$A$1:$C$8,3) but I need for the VLOOKUP to look at the item number as well as the amount. i've been told that I need to utilise concatenate, but I don't know how to do this.
In Excel 2007 I want to concatenate two columns of text. In Column A all the cells contain a single statement that I want to prefix the statements in the cells of column B (the statements in column B differ from cell to cell) I have used the formula =A1&" "&B1 and this is fine for that row, when I use the fill handle and pull it down the page the formula changes accordingly i.e.=A2&" "&B2, =A3&" "&B3 etc. But when I make the text appear using control+ I only get the concatenation of the first row repeated all the way down, irrespective of the contents of other cells in Column B.
View 14 Replies View RelatedI have a series of data in 2 columns...say A and B...that I want to concatinate into a string of data...i.e A1&A2&B1&B2&C1&C2 etc etc....
Can I include this in a macro so that it will continue to concatinate the data until it gets to the end of the list (i.e a blank cell) ?
I need a simple macro to take a list of words in 1 column, and concatenate them with each word from another column
So if Column A had "bird"
and Column B had
food
feeder
house
the output would be
bird food
bird feeder
bird house
I will have many many more words than just "bird" though.
Is there an easy way to concatenate two columns and provide every possible combination?
E.g.,
A B
1 A1 B1
2 A2 B2
3 A3
The function should result in
C
1 A1B1
2 A1B2
3 A2B1
4 A2B2
5 A3B1
6 A3B2
I have a worksheet with 10000 plus entries in ten columns From K to T. I wish to have a macro with Concatenate function to merge certain Columns from these ten columns, in one column with help of input boxes which may ask inputs, about range (from and to ), and number of digits to concatenate in the required column. I use Excel 2003 XP in work but an example is 2007 attached.
View 5 Replies View RelatedI have a list of names with blank cells in Column A cell 2.
I want the programme to scroll through this column until it finds a name, when it does I want it to look in cell offset(1, 1), in this case B3 to see if there is a number. I want this number to be moved to offset (0, 1) AND any subsequent numbers below it until the next blank cell in that column (B).
In the attached image cell B2 will contain all numbers in cells B3:B9 with a / between them. Cell B10 will have number from B11 and B12 will have B13:B15. If there is no number it will just continue to the next name in column A. My rough code below delivered the first number only in the relevant cell and I'm not clever enough to get it right. It does not have to be super slick as it's for one time use.
I need numbers in column B cleared as they are concatenated into the single cell.
Spreadsheet.jpg
How do I create a code that will concatenate the number in Column B, C and D into Column E for each row within each worksheet of the file, excluding the "Update" worksheet. If there is no number is each column of the row, then do not produce a record for that row in column E. Start executing function on row 2.
eg E2=b2&c2&d2=047, E3=b3&c3&d3=964, E4 cell is empty, because there is no record in b4, c4 and d4
I am creating an excel file for a mail merge following this process. I need to take the three columns below and enter the path to the corresponding chart. I have all the charts created in a directory. The naming convention for all of the files is XX-XX-XX.gif. The X's represent the numbers below without the decimal places. Therefore the example below would be 44-43-27.gif. Is there anyway to create a formula to drag down instead of entering the filename's manually? In the end, I want to have the full file path as shown in the XLS.
q_3_abc q_3_teamq_3_yscore
4.4 4.3 2.7
I have 700+ rows to do this for, therefore I want a formula or VBA solution instead of the manual donkey work.
I have a marco that works perfects to Concatenate (join) all of the cells in Column A. However, I want to seperately join all of the cells in each column. So I wanted to the Concatenate of Column B to go in B1, Column C to go in C1, etc (or until there is no data in a column). I think there is a way to change this into a loop, but I don't know how. Below is the macro I have now.
Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("A2:A300")
For Each cel In rng
x = x & cel.Value
Next
.Range("A1").Value = x
End With
End Sub
how to remove multiple leading and trailing spaces while leave spaces in the middle of the string in place? I have tried text to columns but this does not remove the multiple spaces.
View 9 Replies View RelatedI am trying to parse a formatted text file. I am using the SPLIT function as
arr() = split(cline," ")
where cline is an input line. if the line input(cline) has multiple spaces between two words, how would it split that.
Eg :
abcd defg fefdcs fasdfasdsa
would the output of above be
arr(0) = abcd
arr(1) = defg
arr(2) = fefdcs
arr(3) = fasdfasdsa
I think I just need a basic Nested For Loop code. I have a list of in column A, from A1:A537 and another list in column B, for B1:B50. I want to add a column that concatenates, each item in Column A to B1 (so 537 rows), then again each item in Column A to B2, another 537 rows, etc.. this loop is repeated 50 times, one for each name in column B.
View 3 Replies View RelatedI have the following situation and I'm trying to do this using a PC with Excel 2007.
I would like this:
T
N
O
O
C
O
E
I
M
I
T
[Code] ........
To look like this:
T
H
A
N
K
[Code] .........
This procedure will become part of a macro. The number of rows will vary but the number of columns will be the same (11 columns). I don't care too much about the order since I can later sort. As long as the data from all columns in now in one column.
I need for it do perform the same action on multiple rows/ cells of data. What adjustment to the code needs to be performed to do so?
Sub fconcat()
Range("C1").Value = Range("A1").Value & Range("B1").Value
For i = 1 To Range("A1").Characters.Count
Range("C1").Characters(i, 1).Font.Name = Range("A1").Characters(i, 1).Font.Name
Next
For i = 1 To Range("B1").Characters.Count
Range("C1").Characters(Range("A1").Characters.Count + i, 1).Font.Name = Range("B1").Characters(i, 1).Font.Name
Next
End Sub
I've found several posts about returning variable substrings that dealt with one or two spaces, but I have not been able to find anything that is for multiple spaces. The number of characters before the substring will remain constant.
For example:
Income from transmission agreement - 83 subs @ $0.44
In this case I need to extract: 83
Income from transmission agreement - 10,312 subs @ $0.50
Need: 10,312
The formula below works great for counting. I need to use the same formula to CONCATENATE all of the names in B:B. It seems as though I should just replace COUNTIFS with CONCATENATE and change Data!C:C to Data!B:B, but it doesn't work. I tried a search on the web using the title of this thread but came up with very few clues. Is it even possible to CONCATENATE with multiples IFS?
=COUNTIFS(Data!C:C,IF(B2="All","*",B2),Data!D:D,IF(B3="All","="&W5,Data!E:E,"