I have a date of the "Sep-07"in a1 in a2 i have "fred" and in a3 "expenses" when i try and combine them so that i can save the file name as a variable ie sept-07 fred expenses. I get
39336fredexpenses
How do I get the date not to read numbers?
Date:31-Oct-06 (the date is in cell A3)
Formula: "as at"& " "&A3
Result: as at 39021
I need the results to be "as at 31-Oct-06". Is there a formula for this??
I am trying to combine the text values of several formulas in addition to adding some text (punctuation) in between.
Here is the data that I am trying to combine
C14: 2013
C15: Period 6
C16: Week 1
In Cell C13, I would like to have this value returned: "2013 - Period 6, Week 1"
Is there such a way to do this?
- I have excel file with data I need
- I have fixed txt(html) template that i need to integrate Excel information into
- Final result that I want to achieve is saved .txt(html) file with combination of fixed information (text) and data from excel cells.
I need to writing a VBA code for each of above (integrating text & cells, saving results as text)
i want to creat a sheet doing the following
enter the phone numer in cell a1
enter the phone break down hour in cell a2
enter the phone break down date in cell a3
combine the cell a2 & cell a3 in cell a 4
enter the phone fixed hour in cell a5
enter the cell fixed date in cell a6
combine the a5&a6 in cell a7 subtract a7-a4 and show the result in hours
Is there a formula that can be used to combine a date and a time that will be formatted correctly?
For example if I have the following values:
A1: 08/03/12 (Date)
A2: 16:45:39 (Time)
And then I want A3 to be 08/03/12 16:45:39 as the Date/Time. Can this be done with a formula?
I want to combine two cells and add text. Then delete column D.
View 4 Replies View RelatedI want the user to select a currency pair in cell B4, eg, EUR/USD
Then cell R4 will look to see if there is any data in B4 and, if so, return the following value into Cell R4....
=MT4|BID! & cell B4 & "m"
the cell b4 is the bit i am not sure about.
am trying to code a loop but I have no idea where to start.
With the help of you guys I have already got some code from a previous thread which gives me a list of hierarchical data now I want to be able to loop through this and actually have the 'parent' next to it.
For example:
1 Shops
2 Butchers
3 Meat
2 Bakery
3 Bread
I would like to be able to say
1 Shops
2 Butchers Shops
3 Meat Butchers
2 Bakery Shops
3 Bread Bakery
I have a column say column A that holds text. If the values match say:
ABC1 or
ABC2 or
ABC3
And also match column B if it shows "Yes" then show "Correct" or "Not Correct".
I'm trying to write a statement something like: B1 = data. B2=IF(B1/8=0,"",IF(B1/8=INTEGER,B1/8 & "8/8",B1/8+1)). This is meant to identify which relay on a relay board is associated with the given data. For example if data=3, B2 = 1 3/8 meaning the 3rd relay on board 1. If data = 77, B2 = 10 5/8 meaning the 5th relay on board 10. I could do without the 10, but the 5th relay on that board is important to me. In summary, I think my trouble is identifying when the product of B1/8 is an integer. I may also be having trouble combining value and text. Well, there it is. I may very well be going about it all wrong.
View 3 Replies View RelatedIn column A I have product names, in B I have product categories and in C:H the product categories from column B are titles and the name of the subcategory is written in the cell. Because there are mutlipule sub categories that a product can belong to the report produces mirrored lines with one sub category on each line. What I need to do is combine all of these subcategory entries into one cell separated by a comer (,).
View 2 Replies View RelatedI have two columns each column has a combination of the same 3 words "cash", "card", and "trade". The are formatted as "Cash & Card & Trade" or "Cash & Card" or "Card" ect. I want a formual that can combine the entries written into each columan into one column. I will put an example below.
A
B
C
D
1
Client
new
old
both (formula in this column)
2
name 1
Cash & Card & Trade
Cash & Card
Cash & Card & Trade
3
name 2
card
cash
Cash & Card
[code]....
Column A has the following values:
au:asd
au:qwe
au:zxc
etc
I want to combine the text from each cell into a text string so that each cell's text is separated by a comma, thus:
au:asd,au:qwe,au:zxc,etc
I can do this with a formula,
=a1&","&a2&","&a3
but this may exceed the 255 character limit when the text string needs to be transferred to a query.
with a formula that would find a text data point (comma) and round the number to 2 decimal places that is located prior to the text data point (comma) within a string of data. see examples below in Column A and the intended results in Column B.
Column A
row 1: Joe 1.234, Al 6.89656,
row 2: Sam 6.5,
Column B
row 1: Joe 1.23, Al 6.90
row 2: Sam 6.50
I have 4 cells with text in them that I am trying to combine into one cell and not have any blank lines between the text. The cells are arranged like this:
A1="One"B1=Cell where the text is combined
A2="Two"
A3="Three"
A4="Four"
I set the cell alignment in B1 to wrap text and use the following formula in B1:
=IF(A1=,,A1)&IF(A2=,,CHAR(10)&A2)&IF(A3=,,CHAR(10)&A3)&IF(A4=,,CHAR(10)&A4)
The problem is if there isnt anything in one of the cells in column A it makes a blank line between the text in column B. I am trying to get the text to the top of B1 and not have any blank lines between the lines of text. Is there a way to change the formula so that it will do the following three things:
1. If the cell A1 doesnt have anything in it then dont put the value in B1.
2. If A1 has something in it and A2 has something in it then put the text of A1 in B1 and HAVE a carriage return after the A1 text.
3. If A1 has something in it and A2 does not have anything in it then put the text of A1 in B1 but DO NOT have a carriage return after it.
The attached spreadsheet has a "master" workesheet in which I enter customer info, salesperson info, and date. The totals spreadsheet automatically calculates number of sales, contact value.
I need to modify the following formulas to only calculate the data within a date range shown in 2 cells.
=COUNTIF(Master!A1:A176,PayPeriod!A4)
=SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
=E4SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
I have the following VBA code and try to sum the range from B2 to CY2 for DA2; then B3 to CY3 for DA3, etc. Therefore I use the for loop. It results in "Application defined or Object defined error".
View 4 Replies View RelatedI need an Excel 2003 compatible solution for this. Formulae only, no Macros or VBA in this case. The limitation set by the organisation we report to.
On sheet LGFC I have clients with their attendance hours, notes and the relevant dates.
01/07/2013
01/07/2013
08/07/2013
08/07/2013
15/07/2013
15/07/2013
Ref No
Name Carer
Name Client
Active
[code]....
To really test if this works you need to extend this over more than one month. My sheet goes on for a full financial year and of course with rows of clients. I now need to report per month. However, next financial year the month column locations may vary.
We need to report per month.I am able to sum up the month's total hours for all clients (E3 to J26) with the following formula (which ignores text). Data!$G3 gives the relevant month in date format (1/7/2013).
=SUM(IF(ISNUMBER(LGFC!1:1),IF(LGFC!1:1-DAY(LGFC!1:1)+1=Data!$G3,LGFC!3:26)))
array formula: Ctrl Shift Enter
This results (example data) in 10 hours. (if I had a client 2 with zero hours, and a client 3 with another 10 hours it would result in 20 hours)
Now my next step would be to report any notes. So I want to find all the text in that month and concatenate it into one cell. I started with this formula, but that doesn't work and gives a FALSE.
Doesn't work!
=CONCATENATE(IF(ISNUMBER(LGFC!1:1),IF(LGFC!1:1-DAY(LGFC!1:1)+1=Data!G3,LGFC!3:26)))
array formula: Ctrl Shift Enter
Doesn't work!
Ideally I would not just want everything jumbled into one concatenation, but even that would be acceptable. (It is unlikely that there would be more than 255 cells with notes.)
Based on the above example the ideal result would look like:
1 Mrs Test, unwell, left early, Sick
But I would accept just the notes unwell, left early, Sick
It needs to ignore blank cells. With multiple clients it would ideally look like:
1 Mrs Test, unwell, left early, Sick
2 Mr Best, in hospital, visiting family, Sick
or
1 Mrs Test, unwell, left early, Sick / 2 Mr Best, in hospital, visiting family, Sick If we want to include the Ref No and Client Name it probably needs another if statement. "If there is text to concatenate that month/row than include Ref No and Client Name."
combine 2 cells containing text the convert all to lowercase
i.e. in say A1 John, B1 Smith
convert this to c1 johnsmith
this is to me used as a user name generator
then in cell d1 create a 5 digit unique number (password)
I have a data sheet which has a series of dates within it. So for eaxample the data sheet will have date of "01/07/2007" I then want to take this data and create the following field:
"June 2008 Pre Data" The problem I have is when I try to join my variables togeother the code crashes (Invalid Procedure call or argument).
The code is as follows ...
I am trying to build a list of account numbers based on conditions in a spreadsheet. Every account number will look like: XXXXXX-XXX-XXX, depending on if there is a 'Y' in both fields...see attached. I am desperate to get this done and have hit a complete road block.
View 2 Replies View RelatedHow do I combine a formula and text in the same cell in excel 2010.
What I am looking for is the sum of a number of cells, followed by text.
e.g. something like =SUM(E4:E50)"/35"
If the sum result of cells E4:E50 was 10, I would be expecting to see visible in the cell '10/35'
How do I write this formula?
i need t oknow if i can have text added into 1 cell with having a formula with a answer in it as well,,,i have attached a sheet with better examples of what i mean.
View 3 Replies View RelatedAt a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..
Sheet 1
Row 1: Mike β "Great to work with"
Row 2: Mike β "Bad manager"
Row 3: Tom β "Great guy"
Row 4: Mike β (blank)
Row 5: John β "Cool"
Row 6: Mike β "Best boss"
On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.
Sheet 2
="Comments for "&[ref to cell containing "Mike" in another sheet]
- Great to work with
- Bad manager
- Best boss
I have a large list of text in one column which i need to combine between blank rows into one cell or a new column, the number of rows to be combined varies.
for example
aa
bb
ww
xx
zz
rr
gg
hh
ii
jj
would become
aa bb
ww xx zz
rr
gg hh ii jj
I have over 30000 lines so doing it by hand is not an option.
I receive 24,000 text files once a month that need to be combined into one csv/txt file and/or spreadsheet(tab).
About a year ago I posted a thread on the same topic which received a fantastic response from jindon that worked great
Unfortunately, the format in which the text files are ouput has changed, as has the filename layout. The files are now output with filenames such as:
(lic#, company name, displaying # records found, date, type.txt)
40298827_Windham Professionals Inc _Displaying records 1 through 10 of 100_041813_AGENTS.txt
40298827_Windham Professionals Inc _Displaying records 11 through 20 of 100_041813_AGENTS.txt
40303726_HEARTLAND CREDIT RESTORATION INC _EANF_041913_AGENTS.txt
(files with EANF in the filename have no records inside them and can be skipped)
While the contents of each file look like this: (see attached text file reference)
I would like to combine the contents of the text files while appending the lic#, company name and date from the filenames to each record so the resulting file looks like this:
40305196 Audette , Anthony Sales Provider 40298827 Windham Professionals Inc 041813
40313800 Burritt , Kimberly Sales Provider 40298827 Windham Professionals Inc 041813
As far as I can tell jindon's code is fine except the regex expression needs to be modified to handle the new layout, however that is far beyond me.
I've got problem with combining 3 cells into one. First cell contains text, second have date yyyy-mm-dd and last one is as previous. I'm using formula .
[Code] .....
And I'm getting: TEXT_41694_41701. How can that be corrected to use formatting for date format to get TEXT_yyyy-mm-dd_yyyy-mm-dd?
I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
the top is the original, the bottom is what I'd like to get (cells with bold characters). How can I do this using macro?
I need to combine the text in the "invoice" column and put them next to each other. when the length of the combination is more than 15 characters, it should be "payments" instead.