I am looking for a code to insert alt-enter into a cell or a column with a defined comma count per line, saying 5
is
C1,C2,C3,C4,C5,C6,C7
C8,C9,C10,C11
want to change the cell(s) as
C1,C2,C3,C4,C5,
C6,C7,C8,C9,C10,
C11
I have many lines of text and I wondered if there is a formula so I can insert a comma before the first capital letter of each line? A small amount of text is below
leave on left Salter Road
right Brunel Road
What i would like is there to be a comma before the first capital letter so it reads
leave on left, Salter Road
right, Brunel Road
Is this even possible?
I have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:
The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.
A B C
Row 1 November
Row 2 1 5 15
Row 3 1 0 2
Row 4 0 1 3
November is in A3
If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2
I am copy/pasting arrays of numbers from several sheets into a main sheet.
The arrays are placed in separate columns next to each other. However the array sizes (Rows) varies in size
In the main sheet I have placed a =CountA function that counts numbers of non-blank cell within the columns, see sample file.
However sometimes it apparently counts cells that appear to be empty. I mean the cells are visibly empty, but gets counted anyway.
Is there an easy work-around or is it the general use of the formula?
How can a formula perform the equivalent of the keyboard alt enter in a cell?
ie Make =A1&A2 where A1 and A2 are cells containing text become
a1text
a2text
in the new cell.
formula that would count the instances in Column C, where Column A contains "LH" or "RH" as part of the word, and column B equals "Either".
View 5 Replies View RelatedI am an Excel Novice. I don't know very much at all about how it works, what formulas are or any of its intracacies. My job requires me to take information from an Excel file that is emailed to me.
I need to know in the simplest terms how to take the first column (which is last names) and add a comma to the end of each name (one in each cell) all the way down that column, before I copy and paste them, so they will read "last name comma space first name".
I have a list of data in individual cells in one excel column thus...
Item 1
Item 2
Item 3
And I would like the data to read thus in one cell...
Item1,Item2,Item3.
Is there an excel function I can use to do this quickly and efficiently?
I wanted to separate my cell values with comma into a column
Item
DepIDs
No
IDA
1
2000
1
2000
[Code]...
the output will be below table,
Item
IDA
DepIDs
1
2000
2000
[Code]...
I'm looking to insert a comma between letters and numbers like seen below using Windows 7 and Excel 2013. I'd prefer to do this through a formula and not a VBA script. I know this is easy to do if you know how many characters strings are going to be and the format stays consistent.
ABCD90bj10r7
ABCD,90,bj,10,r,7
ABCD90rt8r7
ABCD,90,rt,8,r,7
iFH15jr7ri12
iFH,15,jr,7,ri,12
iFG155jr8ri11
iFG,155,jr,8,ri,11
iFG15jr8ri9
iFG,15,jr,8,ri,9
i'm a complete excel novice, but i think this should be pretty easy for you guys...
i just bought a new satnav system after 4 years with tomtom...
anyway, to get the speed camera database to work with it, i must add a couple of commas at the end of each set of co-ordinates contained within the spreadsheet, like this:
-3.23956,51.52737,1,50,1,67,,
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247,,
thing is, most of them don't have the 2 commas at the end and just end with the number, like this:
-3.23956,51.52737,1,50,1,67
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247
therefore, what i need to do is make up a formula in excel to search for a double comma at the end of each set of co-ordinates and if it doesn't already have them, then it should insert them...
i'm guessing for anyone with excel experience, this should be pretty easy to solve...
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
Original table:
Column1
Column2
Column3
Column4
AAA
DDD
A1
X123, Y123, Z123
[Code] .......
I have a very strange problem in DEBUG mode, because i get this error "Application-defined or object-defined error" when referring to a cell and assigning it a value so it goes to my error handler and i have a Resume Next there. It continues to go through the code whilst continuing to go to the error handler but when i step out of the function it restarts again from the beginning on the called function and then on the second run of my code it seems to WORK!?! So i'm thinking what the hell is going on, it falls over and fails the first time round and works the second time round?
In free-run mode from excel i just get a #VALUE!
I'm not an experienced VBA programmer, so hopefully (and probably) for you it's easy to tell me how to do it: So far, I copy a row(1) to another position (7) with this macro:
Rows("1:1").Select
Selection.Copy
Range("A7:BU7").Select
Selection.Insert Shift:=xlDown
Range("A7:BU7").Select
Application.CutCopyMode = False
Range("A7").Select
That works well so far. Now what I want to do is, to use a form and enter a number and have the row 1 inserted in this row number (because it should not always be in row 7). For example that i can insert 38 in the form and it will copy the row number 1 to row number 38. I tried this:
Range ("frm_insertrow.txt_rownumber").Select
My first sheet shows the data - a list of audits and their current status.
AuditStageAlphaIn ProgressBetaReportingCharlieCompleteDeltaCompleteOmegaPlanning
I've then used a simple count formula to add up the number by each stage and it's these numbers that I want the macro to use when determining how many rows to input.
Complete2Reporting1In Progress1Planning1
A second worksheet hosts the desired output - a simple report template, split into stages for each status type (completed, planning etc.) with the correct number of rows for each.
Completed AuditsCharlieDeltaReportingBetaAudits in ProgressAlphaAudits in PlanningOmega
So, I want the macro to ensure that we have the right number of rows for each stage, as this month will be run on a monthly basis and the stage each audit is at will change, and the number of rows required in each section will also be different.
I am trying to keep my checkbook in excel and want to make a spreadsheet that inserts a row every time I hit enter. That way my most reason transaction and balance is always at the top rather than the bottom of the spreadsheet... The inserted row should have the same formulas as the row below.
View 7 Replies View RelatedI can not remember how to do the following. in column N have numbers ranging from 1 to 100. I need to get numbers counted in this grouping. >50, <50 >70, <70 >85, <85. I don't even remember how to start. I tried
View 2 Replies View RelatedI have one column of data. there are currently ~10k rows, but this will increase over time. each row has either a single value (example: pepsi) or a comma separated value (google,samsung) with up to 6 commas. instead of the rows looking like this:
pepsi
google,samsung
coca-cola
I want them to look like this in one column (preferably via a formula):
pepsi
google
samsung
coca-cola
I would like the above output to be a unique, alphabetized list.
I am trying to use an IF(AND) formula to set conditions where specific values will be counted under certain circumstances.
{=COUNT(IF(AND(Annual_Rate>36000,Annual_Rate
Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?
The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.
I have over 600 rows that needs this done.
1,2,3,4
B
[Code]....
I have a column of dates that are not being recognized as dates unless if I manually select each cell and press enter. For example the cell value is "Jul/13' and isn't recognized as "01/06/2013" until I select it and hit enter. How can I get around this?
View 8 Replies View RelatedI would like to know how to use a VBA code to insert today's date into a specific cell (B9) when any cell in column B (B2:B8) has changed.
View 3 Replies View RelatedI am making a deposit sheet schedule, which has 10-15 batches of deposits that are entered one after the other, vertically. Each deposit entry can be allocated to many different expense accounts, so a deposit will be 500 with 400 to rent, 50 utitilies, etc. So I need multiple summation rows
So, I want to add a sum row beneath each set of deposits.
My problem is that each deposit is a different amount of rows long.
I was thinking of doing something like this:
'totalRow() is each row where the summation formulas will go totalRow(0) is not an actual sum row, but tells me where the first row of data lies.
For Each cell In Range("H" & (totalRow(x - 1) + 1) & "", "J" & (totalRow(x - 1) + 1) & ":N" & (totalRow(x - 1) + 1) & "", "P" & (totalRow(x - 1) + 1) & ":AB" & (totalRow(x - 1) + 1) & "")
cell.Formula = "=sum(Range(""A"" & (totalRow(x - 1) + 1) & "":A"" & (totalRow(x) - 1)"
Next
I don't know how to reference the ranges I want to sum, because I don't know how to increment the reference to the column (i.e. changing A to B to F to G, etc.) with the Range().Formula construct.
I was thinking of using R1C1 somehow but I'm not as familiar and I don't think you can use r1c1 & Range() at the same time.
excel spreadsheet that has data in column A like in the attached example spreadsheet. How can I make this into a comma delimited list? So something like this...
food
drinks
home
shelter
ocean
water
to this ---> food, drinks, home, shelter, ocean, water
I have hundreds of email addresses listed in a Wordpad file- entries are
separated by a comma. When I use the Excel Import function, all data is
successfully imported into an Excel spreadsheet- BUT in a single row. I would
like to have this info in a single column (A)- then I could easily
alphabetize the entries and eliminate duplicates.
I have a macro that creates an email based on the contents of each column in the worksheet. The macro works great, but I would like to format a column (A) which contains numbers into the Comma Style, prior to sending out the email.
Is there a VBA code that will format a given column (A) into a certain style (Currency, Comma, Percentage, etc) ?
I have a list of terms in a spreadsheet. Assume they start in cell A1 and they descend down for the next 300 cells. Basically I need to pull those terms into a single text string where the terms are comma delimited.
What I have been doing is concatenating them so they all end with a comma, copy them 25 at a time, pasting values and transposing then running a concatenate formula for the 25 terms. Do this 10-15 times to create that many comma delimited lists then concatenate those lists to create one all in one list.
Example:
The list (starting in A1) looks like this:
Dog
Cat
House
Car
Boat
Mom
Dad
but I need:
Dog,Cat,House,Car,Boat,Mom,Dad
If possible to do this with a formula please do so as my knowledge of using VBA modules is limited but if this must be done using VBA please realize that I'm in the thrid grade compared to your knowledge so please explain how to implement the module as clearly as possible.
I am looking to insert a row every time any cell in column A = R
This will happen around 50 times.
The inserted row will need to have an R in column A and the number 349273 in column B.
I have a dataset that i want to update, to do this I want to insert 8 rows after each row that has a value in Column C 2000. The set has 600,000 rows, comparing countries (cty, cty2) from 1980-2000. I want to make room for information up till 2008. I see lots of codes for doing this but I will also need help with actually inserting into my spread sheet.
View 3 Replies View Related