Can I put the file name into a concatenate formula?
What I've got is seats for lots of different settees that I want to show up on a cutting sheet as ie:
10 Chatsworth Seats
15 Abbey Seats
At current its is reading 'Seats' from the file Chatsworth but if I can add filename to my concatenate it will say "Chatsworth Seats"
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 list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
Sampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies View RelatedI have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
I have 2,000+ cells containing text that I need to break out into multiple cells. They are names (ex. John M Smith MD) and I need each part of the name in it's own column. I need the opposite of concatenate.
View 3 Replies View RelatedI'm having a rather difficult time getting this one.
I am concatenating (is that a verb?) a bunch of cells to create an item code.
The combination of two different cells need to be able to give different values so I can dump the appropriate value into the CONCATENATE formula.
A picture is worth a thousand..
I am trying to get 2 rows in one drop down without having to create another combined row. im not sure what formula should i use. I have attached an example.
View 7 Replies View RelatedI want to write formula in cells(many cells!) using concatenate. The desired output:
In Sheet2!D1 - i will write concatenate of Sheet1!C6 and Sheet1F6
in Sheet2!E1 - i will write concatenate of Sheet1!C7 and Sheet1F7
in Sheet2!F1 - i will write concatenate of Sheet1!C8 and Sheet1F
Copy and paste formula would not do so i created a macro stated below. The problem is a can not change the ActiveCell.Formula correction.
I have a simple task and I use the concatenate formula to resolve alot of keystrokes. I now have an issue where I have to edit that formula because of a additional character for the string won't upload into a database. Here's my example:
1. Cell A1= 12345xxxx Cell B1= 67 Cell C1= 8
I use =CONCATENATE(A1,"-000"&B1,"-000"&C1) and my result is displayed in D1= 12345xxxx-00067-0008
Easy enough! Now adding the following is the problem:
1. Cell A2= 12345 Cell B2= 67 Cell C2= 89
I use =CONCATENATE(A2,"-000"&B2,"-000"&C2) and my result is displayed in D2= 12345xxxx-00067-00089
Now D2 has one too many characters from C2
I modify the formula by reducing to "-000"&C2 to be "-00"&C2 manually but now the line items have quadrupled and manually isn't going to work for this being so time consuming.
Is there a way to use a variable with the concatenate or am I using the wrong formula period ??
A1 contains "=2+3+4" which shows 8 as a result. I would like to add a word "Lbs" with it so it would display "8 Lbs" on the same cell. If I entered =1+2+3 on cell A6000, it should give me "6 Lbs".
View 7 Replies View RelatedI'm trying to use a macro to write a formula within a column of data.
Here's the
i trying to merge column A1 (ABC) with column B1(XYZ), B2(LOL), B3(ROF) ..etc
with a simple =CONCATENATE(A1,"-",B1) , i can get "ABC-XYZ" , but when come to column B2 , i only can get "-LOL" , any solution ?
Create a column of concatenated values to serve as a "primary key" for a compare and merge tool (Synkronizer). For some reason, the CONCATENATE function isn't being recognized.
If I enter
=CONCATENATE(A2,B2)
instead of showing the strings located in A2 and B2 it displays
=CONCATENATE(A2,B2)
in the cell.
I tried creating a new spreadsheet and it works just fine. Does anyone have an idea of what might be happening? I have compared all of the options between the two spreadsheets and don't see any difference in them.
I have 6 columns:
A1 = LastName
B1 = FirstName
C1 = LastName2
D1 = FirstName2
I need to concatenate into 1 cell so it looks like this: FirstName LastName;FirstName2 Last Name2. Now the challenge is that there are many instances where C1 and D1 are blank (don't have values).
So here was my attempt at a formula:
=IF(A1>""&B1>"",CONCATENATE(B1," ",A1))& IF(C1>""&D1>"",CONCATENATE(";"&D1,"",C1),"")
Now this works, but it ALWAYS leaves a semi colon at the end (even when there is no C1 and D1 to concatentate. Thus I put in my IF statement the "".
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 have this code
Code:
Selection.FormulaR1C1Local = "=INDIRECT(CONCATENATE(""["",R4C26,""]Sheet1!"",RC))"
Which creates this in the excel cell:
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!",D4))
But for some reason, it returns a 0. However, when I change the code in the excel cell to this
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!D4"))
It works. But I need to be able to change D4 in the macro, so doing it that way won't work for me.
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.
Currently I am trying to concatenate two arrays stockotherarray and stockfittingsarray to create stockarraynew
Below is my code, I keep receiving a compile error.
Sub stockarraynew()
stockarraynew() = Split(Join(stockotherarray & Join(stockfittingsarray))
End 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
On tab 2 of a spreadsheet, I am trying to concatenate several cells on tab one! Everything comes together in the cell on tab 2, however it does not hold the date format. It turns it into general text so just random numbers that do not even tie to the date. Any way to lock the date format for this idea?
View 7 Replies View RelatedI have a list containing 3 the abbreviations of states in A1:A3 (one in each cell), and I would like to concatenate the list in cell B1 to display the list like this: "CA, TX, FL". At times, the list will only have one state (always in A1), but at other times it may have 2 or 3.
I tried: =IF(ISTEXT(A),A1&", "&A2&", "&A3,A1)
But, if A2 does not list a state then it returns "#value!"; and if A2 lists a state but A3 does not then it returns only the value in A1.
What can I do to get it to list all states listed in column A whether there are 1, 2, or 3 states listed?
I have 7 consecutive cells which contain a %
e.g. A1 120%, B1 35% etc
I am trying to put them into one cell using concatenate
but am getting 1.20.3529411764705880.705882352941177
I would also like to separate them by comma,
I am using the concatenate function, however my date field, instead of maintaining its 08/07/07 state, changes to the annoying 5 digit 33935 number.
Even if I cut and paste it as special / values, it stays as the 5 digit number. Can this be avoided somehow?
From cell A1 to A10 I have the figure 5412587
In cell B1 I want the figure in Cell A1 to concatenate with the figure one but in cell B2 instead of figure one with figure two etc. etc.
So B1 will be 54125871, B2 54125872, B3 54125873 etc. etc.
the formula would be Concatenate(A1&1). I want the "one" in this formula to be increasing so in B2 will be Concatenate(B1&2) et.c etc.
The problem is that I dont want to add an extra column to replace the 1, 2, 3 etc. in the formula.
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,"
I have a simple concatenate in Excel...
=CONCATENATE(A1, ", ", A2)
Which produces:
This is, my text
Now, I want 'my text' i.e. the contents of A2 to be in bold.
I've played with the TEXT() function before, as per this page:
[url]
To get Excel to play nicely with date formats and stuff, however, my research has come to a dead end with Bold/Italic - with everyone suggesting that VBA is the only solution.
As I don't feel overly comfortable using VBA is there any other solution? (If not, is this a VBA 101 task or an advanced task?)
to concatenate across columns, say J through Q, (some of which are blank) and have the results delimited by a comma without returning anything for columns that might be blank. So R660 would read "0263B001A,FX9,FX-9,FX10,FX-10" and R669 would ...
View 9 Replies View Related2 questions:
1. How can i put an Indirect function nested with concatenate?
=CONCATENATE(A8,B8,C8,D8,E8,F8,G8,H8,I8,J8,K8,L8,M8)
2. How can i get the formula to adjust to new data range without manually filling down. Assume the formula starts CELL Q8