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:
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...
look at my attachment and see what I am doing wrong in my formula? I have a hard time understanding the Sumproduct formula and when to use comma's, double negatives, addition, etc.
The below function concats a range of cells by csv. How can I get it to remove the last comma in the string when it's finished?
Function SpecialConcatenate(rnge As Range) As String Dim r As Long, col As Integer
For c = 1 To rnge.Columns.Count For r = 1 To rnge.Rows.Count If rnge.Cells(r, c) "" Then SpecialConcatenate = SpecialConcatenate & _ rnge.Cells(r, c).Value & "," End If Next r Next c End Function
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.
In filed I have couple of value separated by comma like below:
A1 header1 B1 header2 C1 header3
Audi 592035, 579733, 653749, 579735 20 000
If my macro found that string (always will be separate by ",") should split the string and add rows (= to number of string). The output should be as below:
A1 header1 B1 header2 C1 header3
Audi 592035 20 000
[Code] .....
I have:
Code: Set sourceWb = ActiveWorkbook Set ws = sourceWb.Worksheets(1)
I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found.
For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years.
I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?
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
I'm trying to have excel insert a timestamp on a specific range of cells. I have it currently working on one cell, but can for some reason am having trouble getting it to work on the entire range... Here is what I have currently...
PHP Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = "$E$23" Then Target.Value = Time End If End Sub
I would like this to work for the range E23:E28 and F23:F28 I'm sure its something simple that I'm not doing...
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 have a macro that takes the column of data from D on Sheet 1, pastes it into A on Sheet 3, gets rid of duplicates and then, in B1, combines all the numbers from A into a comma-separated string. The only problem is that it puts a comma at the beginning of the string. Is there something I can do to make that first comma not be there?
I would like to query your knowledge database (too much VBA I guess ) as I have a predicament that I cannot seem to solve.
Here is the thing: I am trying to write a sub that would enable me to automatically put in place extensible name ranges (the Offset worksheet function).
The problem is that, despite everything seems to go well, the reference in the name box displays : ="DECALER($C$2;0;0;5-1;1)" (the string comes from a variable and Decaler is Offset in French). These quotation marks are the culprits for sure, as taken off the formula works beautifully.
I've tried everything I've found on the internet, that is to say: Replace(String, Chr$(34), "") so on and so forth, but these marks are not recognized a part of the string so they escape the replacement.
I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.
I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.
I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.
this formula as a result =IF(LEFT(A3,1)="0",RIGHT(A3,LEN(A3)-1),A3) It worked great to strip the left most zero from a string of text numbers in the cells where the formula was applied.
However, now I need to put the zero back in lol!
So...how would I adjust the formula (or write a different one) to insert a zero as the first digit of a string of text digits?
I am trying to setup a variable from a dropdown selection box. Basically, If "Completed" is selected in the box, current date is set in the corresponding field.
The formula I have tried is: =IF(F5="Completed",G5="",G5=NOW())
I have a variable string setup opposite of what I am going for that works: =IF(AND(D5<>"Assigned",D5<>""),IF(E5="",NOW(),E5),"")
So if anything is showing other than assigned the date is input. But have not been able to reverse this for the desired output.
I have a column called "Unit Size," which contains data such as 200 g, 0.32 g, 15 mL, 2 mg.
I need a macro to automatically insert a space between the numbers and the letters, for example, if someone types 10g it automatically converts it to 10 g.
The strings may be of different lengths, i.e. contain different numbers of decimal places or signficant figures.
In excel macro, I am trying to input data into sql server from excel ADO what is the correct syntax for an insert statement using string sql? This table has two columns supplier ID autonumber and desc as varchar.
I'd like to adapt my macro so that it would insert a blank row after it detects the the first 16 characters of text as "'Closing Balance"; or it could even detect "'Closing" as the first 8 characters if it would be simpler
The Data Begins in Row 5 of Column A
My Current Macro is as below which I have adapted from another one I used
It is not working since it is detecting for the exact text "'Closing Balance" whereas the data registry would write "'Closing Balance as at 31/10/2009" , of which the "as at dd/mm/yyyy" portion would change every time a report is exported, but the first part "'Closing Balance" or even just the word "'Closing" will always be the same.
I've been working on this project to propagate certain products through multiple categories. I chose to use excel to assign multiple categories to each product. On sheet1 I have setup products (column a) and qualifiers in the following columns (color, model, etc). What I would like to achieve is for the user to select yes or no for each category column and and if yes then have the corresponding category breadcrumb string (from sheet2) inserted into one specific column in sheet3. As the user continues to select multiple "yes" from the category columns for that single product, those additional category breadcrumbs get inserted at the end of the list in sheet3.
I need a nudge in the right direction with how to amend the below code so that it : 1: cycles though all cells in a workbook, and sees whether the text reference of the cell contains a picture filename (i.e. searches for .png or .jpg in the cell contents) 3: if (1) is correct, it retrieves the picture from "C:/Users/jeff/Documents/Standards/" and pastes it as a cell comment background in the cell to the right (and overwrites any comment backgrounds that might already exist there)
The code below does something a bit different: it looks in a defined range, then adds a comment with a background picture retrieved based on the text in the cell to the left.
I'm sure this is a pretty basic change, but my VBA skills aren't up to it...I've only just started reading though Walkenbach's Power Programming! I'm using Excel 2007
Sub InsertComment() 'www.contextures.comxlcomments03.html Dim rngList As Range Dim c As Range Dim cmt As Comment Dim strPic As String
On Error Resume Next
Set rngList = Range("A1:A5") strPic = "C:/Users/jeff/Documents/Standards/" On Error Resume Next For Each c In rngList With c.Offset(0, 1)...................
I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).
