Concatenate Formula With If And Text Function?
May 20, 2014
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
View 3 Replies
ADVERTISEMENT
Nov 19, 2013
I am trying to join text in two different cells using concatenate function / & operator In the combined cell I want the text from second cell to appear in Bold while the text from first cell should continue to remain in normal font. Is there any way i could achieve this?
View 4 Replies
View Related
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
Jul 21, 2006
Is there a way to make the CONCATENATE formula put all the text on different lines? It keeps combining some on the same lines and I want them all on separate - like a list.
View 13 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
Jan 23, 2010
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 Related
Aug 8, 2012
I 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?
View 9 Replies
View Related
Jan 29, 2009
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 Related
Feb 16, 2010
I 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
View 2 Replies
View Related
Mar 28, 2007
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 Related
Feb 23, 2010
I 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 Related
Nov 7, 2012
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))"
View 2 Replies
View Related
Dec 18, 2007
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?
View 14 Replies
View Related
Jan 14, 2009
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 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
Mar 26, 2014
I 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 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
Jul 12, 2013
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?
View 5 Replies
View Related
Apr 22, 2014
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 Related
Aug 1, 2008
I 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.
View 14 Replies
View Related
Feb 4, 2014
I have a problem with an array formula where I want to sum the values in every 7th row. The problem I have is I am receiving #VALUE! in my returing cell of the formula when there is text in the array. Is there a way I can avoid including the text in the array or maybe error checking using ISERROR and ISNUMBER.
The array formula I am entering is: [Code] .........
View 4 Replies
View Related
Jul 11, 2014
with Match function not matching text in this formula?
Using the below formula cell $L$11 (it contains "abc") will not match the respective cell contents in array E:E even though "abc" is in a cell within column E.
=IFERROR(INDEX('Paste In Data File'!G:G,MATCH($L$11,IF('Paste In Data File'!C:C=B14,'Paste In Data File'!E:E),0)),"")
The cells within column E are populated using a formula such as =IF(MID(A6,3,1)="a",MID(A6,435,9),"")
Column A is the data source and contains a text string.
After much trial and error, it seems the cause preventing the match is; $L$11 contains typed in text "abc" and the above formula for column E is pulling in 9 characters, not just 3.
It seems that if I can modify the entry for the array E:E to just look at the left 3 characters only, it may work.Problem is I do not know how to modify E:E accordingly.
View 2 Replies
View Related
Dec 8, 2007
I have the following formula: =TEXT(( SUM(D98,D117,D136))/12,"0")&"' - "&MOD((SUM(D98,D117,D136)),12)&""""
This formula adds the inches in D98, D117 & D136 and divides it by 12 to get the footage of the 3 cells. The problem occurs when you reach the half foot mark...for instance say D98 was 2-0 (24 inches), D117 was 3-0(36 inches) and D136 was 2-6(30 inches) added up this comes out to 7-6(90 inches)...the formula then rounds the footage up to 8 feet...this can understandably be frustrating when I'm trying to get an exact measurement...is there a way to force the formula not to round up?
View 7 Replies
View Related
Mar 19, 2009
I want to concatenate two columns in a separate tab but when I do, the values appear as ....
View 9 Replies
View Related
Jun 21, 2006
VBA code to use input box result in a Concatenate function.
Sub test()
Dim DistName
DistName = Application.InputBox("Enter District Name")
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-2]C[1],"" Diabetes Dist"")"
End Sub
I want to replace R[-2]C[1] with the result from the input box.
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
Feb 13, 2008
=IF($A2="","",IF(O$1="","",VLOOKUP($A2,HQA01!$A$24:$AG$525,6,FALSE)))
The above statement works great, but when I try to replace the HQA01 (worksheet name) with a cell reference it doesn't work anymore. I want to point to the cell that has that name of the sheet in it rather than hardcode each sheet name.
=IF($A2="","",IF(O$1="","",VLOOKUP($A2,$Q$1&"!$A$24:$AG$525",6,FALSE)))
When I try the &, or the concatenate function it appears to put quotes around the entire result
View 9 Replies
View Related
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
View Related
Jul 23, 2014
how to properly write this kind of commands!
v_1 = Sheets("WB_Input").Cells(i, 2) 'It's gonna be a numeric value like 10
v_2 = "E"
v_3 = Sheets("WB_Input").Cells(i, j) 'It's gonna be a numeric value like 12
Sheets("WB_Output").Cells(i, j) = "=CONCATENATE(" & v_1 & "," & v_2 & "," & v_3 & ")"
Thing is once I look at the WB_Output Sheet what I see is an error and the cell shows this: =Concatenate(10,E,12)
So logically the problem is that I need to make it show up as =Concatenate("10","E","12")
Btw, I can't just put v_1 & v_2 & v_3, cause Excel understands "E" as exponential!
View 1 Replies
View Related
Aug 24, 2014
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
View 3 Replies
View Related
