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.
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'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...
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.
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).