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 RepliesI 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)
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.
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 Relatedhow 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 RelatedI 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 RelatedI 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:
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.
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.......................
What function can get me the first letter from the first name and the whole last name together and then add @email.com. Example: Hanry Jones = HJones@email.com
View 2 Replies View RelatedI use a existing code and want to add the Concatenate in the code.
VB:
Sub Subtotal()
Dim myAreas As Areas, myArea As Range, x As String
Dim y As Long
Worksheets("INVOICE_hulp").Activate
Range("Q1").Select
[Code] ....
The array sum function works but Concatenate not. In Excel I get the formula Concatenate(H2:H3) in stead of Concatenate(H2,H3). What should I change to get the code working?
I have data in cells A1:A50. I want to combine all of them into cell A51. I know that I can achieve that by using =concatenate(A1,A2,A3,A4,........,A50) or =A1&A2&A3&A4&......&A50 but it would be very tiring to click on each cell. Just imagine if the data in cells A1:A1000? function that work like say =combine(A1:A50).
View 2 Replies View RelatedI am using the function =CONCATENATE(A2,B2,C2)
Why is it dropping the 0 from B2?
Table 1 Table 2 Table 3
C 012 A
C12A
Trying to do Vlookup with If but dont know how , I can do the concatenate function and then Vlookup as in Sheet 3 but that is too much of load to deliver in quick time specially i have pull data in dynamic and continuous update.
View 14 Replies View RelatedI want a report header to be similar to: REPORT AS OF 2/23/10. If I use the TODAY() function by itself, I get the date; 2/23/10. But when I concatenate it with the text "REPORT AS OF " & TODAY()" I get REPORT AS OF 40232". How do I preserve the date format when I concatenate it with text?
View 2 Replies View RelatedI try to merge two sting (one from function). the output of the following sub should be "ttgood". but instead, i got "good" only.
Code:
Function cs()
Dim sv As String
sv = "tt"
End Function
Sub ts()
Dim s As String
s = sv & "good"
MsgBox s
End Sub
I've succesfully managed to get the mail merge functioning from within Excel at the press of a button. I had a problem initially Word not shutting down properly but have solved this.
The problem I have now is that after the merge has finished and the spreadsheet is saved/closed and Excel is closed, the process tab in Task Manager says that Excel is still running. I found other instances on the net in other forums of people having a similar issue but alas no solutions. The full code for the procedure is below:
Private Sub cbMerge_Click()
Dim wrd As Word.Application
Dim mydoc As Word.Document
Dim lastrow As Long
Set wrd = CreateObject("Word.Application")
wrd.Visible = True
Set mydoc = wrd.Documents.Open("U:TSD3-Projects11-CBRNCBRN-036-CVF HVAC Main Contract-T13212-WorkingECR FolderTESTINGEQR Test.doc")
With wrd.ActiveDocument.MailMerge
.OpenDataSource Name:= _
"U:TSD3-Projects11-CBRNCBRN-036-CVF HVAC Main Contract-T13212-WorkingECR FolderTESTINGECR Log TESTING.xls", _..................................
I am trying to concatenate the text of a few cells in a specific worksheet. I'm not wanting to use the STRING & STRING type code because I already had a Concatenate formula nested with an index formula and needed to have this formula copied to a certain RANGE, Where the RANGE was specified by a Variable. T
he Code below does what I need if the CONCATENATE/INDEX formula is in the cell that im copying already. (BELOW) The Formula in the cell, for example, on row 19 of worksheet "COMMISSION", that needs to be copied and incremented down the VARIABLE RANGE is
=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1)).
VB:
Dim iInput_Rows As Integer
iInput_Rows = Worksheets("Workspace").Range("D3").Value
Dim iOutput_Rows As Integer
iOutput_Rows = Worksheets("Workspace").Range("D1").Value
Dim iAnalog_Rows As Integer
[Code] ....
My problem is when I use the following code to insert that concatenate/index formula, through vba, then I get errors because it evidently doesnt like the " " for the space i needed between texts. (The Formula is concatenating text in those cells but every other Row) Can I do this in VBA?
VB:
Worksheets("COMMISSION").Range("B19 + iOutputs_Rows").Formula = "=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1))"
I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:
=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")
However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.
It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))
I think it is trying to continue doing something with the text function
how i get this to work?
B4 = BMEF3
function:
=CONCATENAR("=TZMS|";B4;"!VAR")
and this its what should do:
=TZMS|BMEF3!VAR
how im supposed to do that function work?
I try to concatenate some 5 text strings and I would like to have a carriage return after each text string (ALT ENTER). How can I achieve this using =CONCATENATE(......;......;......;......;......)?
View 4 Replies View RelatedI need to create unique IDs for all of my stock. I would like to use the concatenate function so that the first letter of each products type is taken and then joined onto a unique number. Im not sure how I will do this I was thinking that the formula looks at the largest value and adds 1 so that there are no duplicate numbers added to the first letter of the product type. E.g. the first mother board added will be "M1" the next will be "M2" as the formula has found that M1 exists and has added 1.
View 3 Replies View RelatedI 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).
Possible to use concatenate function to enter a formula to a cell and have the cell show the result?
I enter the following in a cell:
=CONCATENATE("=LEN(""",A2,"""",")") 'A2 contains the string "Exchange rate: 925"
what is shown in the cell is:
=LEN("Exchange rate: 925")
If I manually enter the above len function in the next cell, the cell displays 18.
Is it possible to use the concatenate function, maybe combine with other functions, to display 18 in a cell?
each cell contains one word say hello in a1 and Henry in b1. Is it possible to underline Henry after =concatenate(a1,b1) ?
View 4 Replies View RelatedI have two texts in two cells. e.g in A1 I have JOHN, and in B1 I have SMITH.
I need to cocatenate these two texts in a third cell (=CONCATENATE(A1, B1) /or I can use = A1 & " " & B1). It's fine till I concatenate.
But I need the output in the following format:
JOHN SMITH
The second text needs to be in ITALICS.
how can merge 2 column together:
column time1 with text as 00:41
column time2 with text as 27
how can i merge it into a single cell in hh:mm:ss format , 00:41:27 ?
I am looking for function which allows me to merge and sum values in rows with same ID.
Example:
Sheet1
Sheet2
ID
Value
ID
Value
1.1
1
1.1
4
[Code] ........
Ordinarily I would use a Pivot Table and GetPivotData function, which can easily do this calculation, but in this case file I work with is shared, so Pivot Tables do not work. Unfortunately I am not skilled enough to type my own VBA function. New rows are added to the data source (sheet1) every day...
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