CONCATENATE Function To Merge 2 Strings Together Into A Single Cell
Apr 20, 2009
I have been using the CONCATENATE function to merge 2 strings together into a single cell. However the problem is, the third concatenated cell depends on the other 2 existing. The idea is I want to take 2 columns of data, and use a formula to merge them, then delete the original 2 data columns so I have a single column with the full data merged.
COL 1 (A3) = First Name
COL 2 (B3) = Last Name
COL 3 (C3) = Full Name (wanted)
So I do =CONCATENATE(A3,B3) and that puts the fullname in C3, but I want to sort of "flatten" C3 so that I have the fullname by itself and I can delete the original cells (A, and B)
View 5 Replies
ADVERTISEMENT
Jan 11, 2010
I have been trying to use the concatenate function to join some text strings to a cell on a different sheet - From an input sheet -Sheet 1 named Input, to a Notification Form (Sheet 2). Although the Function Argument display tells me that it will display the result I want it actually displays just the formula. It's a very simple thing
Address 1 10 Downing Street
Address 2 Westminster
Address 3 London
Postcode SW1A 1AA
I want displayed as: 10 Downing Street, Westminster, London, SW1A 1AA in a single merged cell. All I am getting on the Form is =concatenate(Input!c25," ",Input!c26," "Input!c27," "Input!c28). I feel sure that it is a very easy solution but I can't arrive at it!!
View 3 Replies
View Related
Aug 14, 2009
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 Related
Jun 23, 2014
I am attempting to count up the sum of names that will be submitted, through a web form, in a single cell.
Example:
Cell B1 will have a bunch of names submitted to it. let's say 10: Marino, Elway, Namath, Montana, Manning, Brady, Romo, Bradshaw, Favre, Aikman. (these text strings will be all in one single cell)
I will have a list of names that are correct to compare to: Montana, Brady, Bradshaw, Manning. I can put those in its own cell to compare to. (C1)
How do i get the sum of 4 from B1 cell? If B1 only has 3 of the 10, how would it return the sum of 3?
Example:
A1 B1 (Submitted cell) C1 (List to compare to "right answers") D1 (sum from B1 compared to C1)
user 1 Marino, Elway, Namath, Montana, Manning, Brady, Romo, Bradshaw, Favre, Aikman Montana, Brady, Bradshaw, Manning 4
user 2 Marino, Elway, Namath, Montana, Manning, Brady, Favre, Aikman 3
This is I am sure a very newbie question, but I couldn't find anything on multiple text strings within one cell, and counting a sum of those.
View 14 Replies
View Related
Jan 18, 2011
Here is the set up:
I have a calendar sheet where the dates go down the rows, and there is one column per member of staff. The staff is split in to three streams (different apps to cover, etc).
In the calendar, we mark a "C" for when someone will be on-call, R for when they will be doing a release, and so on.
In a separate sheet in the workbook, rather than having people look at the whole calendar of 30+ people to see who is on what shift for a given stream, I have an On-Call Rota where the rows are again the dates, and the columns are the streams. In each cell of this sheet, there should be the names of all the people of that stream who will be on-call for that date, so a concatenation of the actual staff members from the calendar, separated by CHAR(10).
I have been unable to find a combination of INDEX, MATCH, LOOKUP, VLOOKUP, etc, etc that gets me past the main problem: they only ever match the first C (or the first R for the On-Release Rota). I can't get a formula for a single cell in the rota that will return all the names where there is a C in the calendar in such a way that I can concatenate them.
Here is the kind of thing I have had to do .....
View 7 Replies
View Related
May 23, 2013
I have a spreadsheet that has relatively clean data, but at the end of every row is a long notes fields (often filled with several paragraphs of text). I'm trying to search inside each one of those notes to see if it contains certain boilerplate language / legalese / key words.
If I was just looking for one word, it would be easy -- I would write =IFERROR(IF(SEARCH("keyword",E2)>0,"Yes",""),""). That way, if the keyword is present, it returns "Yes", and if it's not present, it returns a blank.
What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return a "Yes".
So I could do something like =IFERROR(IF(or(SEARCH("keyword1",E2)>0,SEARCH("keyword2",E2)>0,SEARCH("keyword3",E2)>0,SEARCH("keyword4",E2)>0)),"Yes",""),""), but that seems horribly inefficient. Especially since my list of keywords is likely to change over time.
So what I want it to do is search each cell by simultaneous reference to an ever-changing table of keywords (call it [KeywordTable]). And I can't figure out how to do that. The search function is resisting all of my efforts to put multiple search values / a range of words inside of it.
To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [KeywordTable]", and if the answer is "Yes" return yes, and if the answer is no return no (or blanks).
View 7 Replies
View Related
Dec 21, 2007
I have one row per day of the month in column A. each row has one or more cell entries in column B .... to column (variable) Right now I use a formula =concatenate(B2,CHAR(10),C2,CHAR(10),D2.....) for as long as it has to be, but this has its limits and requires a lot of editing as the number of filled cells for each row vary.
would it be possible with some macro code to manage the variable number of cells and then insert verything, wrapped text, in a single cell (can be cell Bx or a cell in a new column added next to the dates column) As soon as everything is in the single cell, the original data cells can be emtied. I can record a macro but that does not allow me to manage the variable number of cells . a small sample file attached.
View 7 Replies
View Related
Sep 19, 2009
Here’s an example of my data:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
4330DDDDDDD
5330EEEEEEEEE
6440FFFFFFFFF
7440GGGGGGG
8550HHHHHHH
I need your help to figure out how to merge the above data to look like this in new blank worksheet using a VBA macro:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
DDDDDDD
EEEEEEEE
4440FFFFFFFFF
GGGGGGG
5550HHHHHHH
View 10 Replies
View Related
Jan 31, 2010
For example: I want to sort through a a bunch of textboxes and refer them to certain cells. I know I can have a string "TextBox" and then the looping number "nbr" and merge them into for instance TextBox1. But I can't figure out how to use this as a command. Like
View 2 Replies
View Related
Aug 10, 2007
I am writing a macroI have three variables named "name", " book", and "count". In previous statements these variables have all been defined as String, String, Integer respectively. I am trying to uses all of these variables in the same formula and am encountering issues. The formula I wish to use it in is:
ActiveCell.FormulaR1C1 = "=COUNT('[" & book & "]" & name & "!'""B6:B" & count & ")"
Where if book = Book1 ; name = "Sheet1" ; and Count = 100 I would want the following result: "=COUNT('[Book1]Sheet1!B6:B100)"
View 3 Replies
View Related
May 14, 2007
I have several hundred cells that I would like to merge into one cell seperated by a "/".
Concatenate usually works, but I got a big fat NO WAY! from excel when I tried to do too many.
View 9 Replies
View Related
Aug 20, 2013
I am trying to concatenate multiple strings of text where each may have composite or compound formatting. Is there any way to maintain the formatting? The Characters.Insert method won't work because the resulting string is greater than 255 characters. See an example below.
String 1: When you simply concatenate
String 2: these strings, Excel discards
String 3: formatting at the character level.
Required Result: When you simply concatenate these strings, Excel discards formatting at the character level.
View 5 Replies
View Related
May 14, 2013
Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
View 9 Replies
View Related
Dec 23, 2009
i need to create a macro that is dynamic.
i have attached a example on here.
My main idea is that i have to select the rows/column to compare that they are the same for the selected column then concatenate and merge.
In my example, i wan Column B be like Column D... but in the end i wan it to be like Column F if they are the same.
View 14 Replies
View Related
Jun 26, 2014
I have the following formula:
=CONCATENATE("EXP: ",TEXT(AK2," MM/DD/YY")," ","VP DATE ",TEXT(AL2," MM/DD/YY"))
But if both cells are empty I get : EXP: VP DATE
If only one is empty, i get EXP: 08/26/19 VP DATE or EXP: VP DATE 06/27/14
But I do not want EXP: or VP Date text to show if the content of the AK# or AL# is empty. I figured I will place something like If(ak#="","",
But it give me a "Too many arguments" error.
View 4 Replies
View Related
Oct 14, 2009
I am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.
View 4 Replies
View Related
Sep 3, 2008
how can i merge Date 1 and Time 1 into a cell ? And give me mm/dd/yyyy hh:mm format? And then a subtraction on 9/3/2008 0:00:40 - 9/2/200823:31:25, which give me a hh:mm:ss result?
View 5 Replies
View Related
Dec 9, 2008
I have some records. I want to merge them through macro in a singel cell, like I have selected a range then I want to merge them in active cell with a comma.
View 9 Replies
View Related
Jan 10, 2012
I need to concatenate all cells filled out in a column (for example A in sheet1) in a cell in sheet2. More exactly.
column A (Sheet1) (Sheet2)
aaaa cell(2,2)=aaaabbbbcccc
bbbb
cccc
If I use a VBA macro I can write this VBA code
RowsNumber = Application.CountA(Sheet1.Range("A:A"))
Then loop from 1 to RowsNumber and concatenate values in cell(2,2) but I need, if this is possible, to define a FUNCTION (nested functions) in cell(2,2).
View 2 Replies
View Related
Feb 2, 2009
I am attempting to join together up to 32 arrays. I haven't even managed to join 2! can it be done in excel 2003 VBA? the arrays are 2 dimensional (x Rows, 3 Columns).
I have tried this but just produced empty variants:
View 3 Replies
View Related
Mar 8, 2008
Need to take column J20:J255 and column K20:K255 and concatenate into activesheet K20:K255. This needs to happen when OptionButton1.Value=True. The information in each cell will be different. The following code works well, but it will not allow me to put a space in between the two strings.
Private Sub OptionButton2_Click()
Dim DescriptionCell As Range
Set DescriptionCell = ActiveSheet.Range("D20:D54")
If OptionButton2.Value = True Then
With DescriptionCell
.NumberFormat = General
.Formula = "=CONCATENATE('Bill of Materials-3'!F20,'Bill of Materials-3'!I20)"
End With
End If
End Sub
View 2 Replies
View Related
Aug 31, 2012
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.
View 1 Replies
View Related
Dec 20, 2012
I am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID
VOL
001
01
001
05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique)
VOL (concatenated)
001
V01, V03, V05
002
V01, V04
003
V06
PS: I have people using this table with office 2003, so compatibility is necessary...
View 1 Replies
View Related
Jul 27, 2009
I have two workbooks that I'd like to merge together into a single workbook. Or, pull the worksheet out of one and insert it into another.
View 2 Replies
View Related
Jan 17, 2014
I have a CSV file that varies in length. The request is to have a macro to import the CSV file, sort it by account number (COL A) and for every row that has a like number in COL A, sum the numeric values in COL F. I have considered a pivot table however we then have to take the SUMMED records and export then in CSV.
how to SUM based on COL A? The rest of the information in each like row is duplicated.
View 1 Replies
View Related
Oct 16, 2008
Through a query I extract data from an oracle database. This database creates a record for every unique Article_Batch_Pallet_Faultnumber combination. Through a filter macro I create a new format on a different worksheet which creates a record for every uniqe Article_Batch_Pallet combination. I need a searchfunction to get the faultnumbers in the same row.
View 2 Replies
View Related
Jun 30, 2014
In a workbook I have a tab with multiple columns containing names, activity codes, billed hours, etc. On another tab I have created a budget for which I use the Excel SUMIFS function to check the database in the first tab for relevant entries and add them together in the budget sheet.
Now here's for the tricky part. On the budget sheet the names are not mentioned, only the job titles. So these are grouped together. This means that the first tab shows only names, while the budget tab only shows the function title.
The function I have used thus far is this:
Formula: [Code] ......
The first part refers to the column containing the billed hours, the second part refers to the column containing the names (criteria range 1), the third part refers to a specific name (criteria 1), the fourth part refers to the column containing the activity codes (criteria range 2), while the fifth part refers to a specific activity code (criteria 2).
This works really well as long as the budget tab only mentions the names, yet it only aggegates at the job title level. Would it be possible to use the same SUMIFS function to search the database for, say, all the names that fall within one specific job title? Essentially that would result in this somewhat bulky function:
Formula: [Code] .....
Clearly, Excel doesn't allow this.
View 9 Replies
View Related
Jan 29, 2010
I have a excel sheet where I have a list of strings seperated by a "-". I am looking to take the particular cell value and swap the characters before the dash with the characters after the dash. So for example:
FG03-FJ04 would become FJ04-FG03
AB02-CD12 would become CD12-AB02 and so forth....
I have tried findling around with the split function but cant seem to get anywhere, does anyone know a tried and tested method of doing this?
View 2 Replies
View Related
Mar 7, 2006
Excel vlookup function returns #VALUE when the lookup value exceeds 256
characters. I need a hash function to transform large strings into a value
that does not exceed the 256 character limit.
View 14 Replies
View Related
May 8, 2007
i've tried searching for "Mode Function in Text" , "Mode Function by Text" but to no avail. Can someone please guide me in how to do a Mode by Text? =MODE(number1,number2,...) But if i have a whole chunk of data in Strings in cells Range A1:C50
1) How can i get the mode of that Range ?
2) is it possible to get the statistic of those for e.g.
Apple 20
Pear 30
Watermelon 50
etc......
View 4 Replies
View Related
