I am needing to create a sequential serial number using a format yyyy-nnn, where yyyy is the current year and nnn is a sequential number (2007-001). I've tried using both a formula and custom formats but cannot get the cell contents to display as desired. I've used TODAY()&"-"&"001", which gives a valid result of 39100-001, but this not what I need. I've tried many other combinations in the custom option for formatting the cell.
I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)
I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.
i have the required date as part of a string, which i need to pull out and then format it as "yyyy-mm-dd hh:mm:ss" and put it in another file which will be saved as .csv
Here is the code i am trying to modify without much success so far
Sub dateFormat()
Dim dtMydate As Date Dim sMydate As String Dim strWhen As String
strWhen = "As on 13-MAR-2007 14:45:59 Hours IST" ' the starting string 'strWhen = Cells. Find("As on")
'take out the date portion from strWhen strWhen = Mid(strWhen, InStr(strWhen, "-") - 2, (InStr(strWhen, "Hours") - 1) - (InStr(strWhen, "-") - 2))
dtMydate = CDate(strWhen) Debug.Print dtMydate 'returned value is 13/03/2007 2:45:59 PM
dtMydate = Format(dtMydate, "yyyy-mm-dd hh:mm:ss") Debug.Print dtMydate 'returned value is again 13/03/2007 2:45:59 PM...
I have a lot of data in the following format: 20080531T085930. Formatting the cells with yyyymmddThhmmss doesn't seem to be working. What's the correct way to handle this?
I'm trying to use the Date Time Picker control as a time picker and only want hours and minutes to show. Can this be done with its custom format property (Example)? The usual number formatting string doesn't seem to work.
I was reviewing the "Create Custom Menu Items in Excel VBA" code located at [url] and cannot figure out something. How do I add a menu dropdown that contains MORE than one submenu item? This is the code my question pertains to: ...
Can this be done automatically in a worksheet using the custom cell format function??.... because then it wud get formatted as necessary whenever someone enters a vehicle no. in that particular column.
I have a sheet which uses a 'Custom Format' in column A, '00000' to give each row a unique reference. Is there any way to do a 'Find' using this format, e.g. 00012, rather than just 12. Searching using '00012' doesn't find the appropriate cell, whereas using '12' does?
I'm having trouble with the leading zeros of my ISBN#s; excel keeps chopping them off. I found a topic that discussed this issue, but it won't work for me.
I can't format them as text because I'm getting them as an isbn with dashes (ie:978-05689-256-7) and when I remove the dashes (even if its formatted as text) excel changes the number to 9780568.9E+12 (or something like that)
I think the solution would be to create a custom format (ie. 0000000000) to preserve the zeros, but I'm having 2 problems:
1. Since the ISBN#'s have 2 different formats (ISBN-10 & ISBN-13) I need to somehow check if the first 3 numbers are 978 then based on that it should be formatted either as ISBN-10 (0000000000) or ISBN-13 (0000000000000)
2. In the post that I linked to above it said that I won't be able to do a vlookup if its custom formatted. Is there any way around this?
I have a column in my sheet which I need to be displayed with dashes.
Excample: 123456 -> 12-235-6 (just an example) But once in a while in the code is a letter like: 123A56 which obviously I need to be displayed like 12-3A5-6
But whatever I do, nothing seems to work. Another cell with Left()-Mid()-Right() is no option for me....
Anyone know if a wildcard can be used in Custom Formatting using text leading off. Example:
U01234567
I have the above custom formatted as: "U"00-00-0000 which gives me U01-23-4567. This give me the format I need however; the U is not always constant and needs to be changed to different letters as required.
I have a custom format that is applied to column AU. Here is the worksheet code which is placed in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column <> 47 Then Exit Sub If Target.Value = "" Then Exit Sub On Error Goto Errhndl Application.EnableEvents = False Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & ">" Application.EnableEvents = True Exit Sub
Errhndl: Application.EnableEvents = True
End Sub
It works well on the sheet, if I remember to only put three numbers, three letters and then two more numbers (allwithout any spaces. Lowewr case ok). (eg. 122rmm25 is then transformed into Map 122R <MM-25>). On the sheet this works. Coming off of the UserForm, it tries to apply this format to text already formated and messes up royally!!!
Is there any way to either reverse this proccess coming off the UserForm or to stop the worksheet from appling this format to the value of the cell in question if the source is in the wrong format to be changed? How do I detect what format the text is in? (Raw, formatted correctly or over formatted)
I need a code to sort worksheets in a huge workbook. Is this technically possible!?
The information containing the worksheet order is available in worksheet "Aux", column A contains the rank (1,2,3,...etc.), whereas column B contains the corresponding worksheet names.
I am trying to create a custom sort list by going to preferences/custom lists and typing 37 items into a new list in Excel. It will not take all of the items after I type them in. Is there a limit as to how many items can be in the list. It seems to only take 18-20 items out of the 37 and erases the rest.
I am trying to create a msgprompt but instead of using the default yes or no, I need 3 options, so if the user clicks the first option, it will check a certain box. It will look like this.
I am trying to create custom sort list. It works below when I define range as A1:A79.
Sub SortWS2() Dim SortOrder As Variant Dim sheetsorder As Range Dim Ndx As Long Application. ScreenUpdating = False With Worksheets("Sort Order").Range("A1:A79") For Ndx = .Cells.Count To 1 Step -1 Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1) Next Ndx End With Application.ScreenUpdating = True End Sub
I have created a dynamic range called sheetsorder. If I revise my code it does not work.
I have created an Addin from one I found on this site which creates a menu on the CommandBar. On this menu I can add my Macros. The Menu is initially supposed to perform a delete function then an add, just in case the menu already exists on the CommandBar. It is also supposed to Uninstall the menu when you de-select the Addin. At the moment it doesn't seem to be doing either, as I have now got 5 CommandBar menu's all the same and I can't delete them?
This also creates a problem of when I add another macro, the menu on the CommandBar doesn't update with the addition?
I have attached the .xla file for you to look at and see where I'm going wrong.
I'm looking at what appears to be a custom chart. It is a box divided into 4 equal quadrants. In each quadrant is a percentage with the total equaling 100%. In each of the quadrants there is fill equal to the percent that is numerically written in that quadrant. I'll post a picture with this, but if I was talking cells then....
A1 = 15% B1 = 66% A2 = 4% B2 = 15%
The backround image behind the number is filled up by volume equal to the percentage indicated. They are also color coded with red representing the highest percentage quadrant filled.
To top that, there are little arrows along the sides of each quadrant indicating an average score.
My first question is WHAT TYPE OF CHART IS THIS THING!, and second can this be done in excel?
I am trying to create a number format for formatting resistor value in their most commonly displayed form. For example 1,200 ohms would be displayed as 1.2kÙ and 5,000,000 ohms would be displayed as 5MÙ, and finally 5 ohms would be displayed as 5Ù. I have been able to create a format that formats any 2 contiguous formats but not all three.
for example: [>999999]###.0,,ÌÙ;[<1000000]###.0,kÙ; or: [>999]###.0,kÙ;[<1000]###Ù
both work.(other similar criteria work as well) But if I try to put 3 conditions describing the desired formatting I get and error (containing no useful info) as I try to close the formatting dialog. Is there a limit to the number of conditions that can be used. Or something else I'm overlooking.
is it possible to custom format a cell in order to:
a) Present 3.33333 as 3.33 (max. of 2 decimal digits or less) b) Present 3.333 as 3.33 c) Present 3.33 as 3.33 d) Present 3.3 as 3.3 e) Present 3 as 3 (NO zeros and NO decimal dot)