i have a long column pair of data, each entry in its own cell:
10/5/20088:30:00 AM 10/5/20088:46:00 AM 10/5/20089:14:00 AM 10/5/200810:18:00 AM 10/5/200810:42:00 AM 11/5/20088:30:00 AM 11/5/20088:46:00 AM 11/5/20089:14:00 AM 11/5/200810:18:00 AM 11/5/200810:42:00 AM 12/5/20088:30:00 AM 12/5/20088:46:00 AM 12/5/20089:14:00 AM 12/5/200810:18:00 AM 12/5/200810:42:00 AM 13/5/20088:30:00 AM 13/5/20088:46:00 AM 13/5/20089:14:00 AM 13/5/200810:18:00 AM 13/5/200810:42:00 AM 14/5/20088:30:00 AM 14/5/20088:46:00 AM 14/5/20089:14:00 AM 14/5/200810:18:00 AM 14/5/200810:42:00 AM
how can i program a macro to 'split' this column according to date? please refer to the attached picture as an example. i know this is probably a simple question but please bear with me i'm still new to excel programming.
New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:
(NOTE: does not contain actual names or info) Book1.xlsx
This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?
While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.
Column 1 has 1200-1209,1300-1350,1523-1563 Column 2 has 1400-1409,1600-1650,1823-1863
I would like to take the range of e.g. 1200-1209 and have excel put 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 into separate adjacent cells for me. And be able to do this for each column/cell of data I have like this.
If this has been requested already, I apologize, please direct me to the right thread and flog me with USB cable. I've been searching the forum for an answer for a couple of hours now for the answer to this:
I have a list of words in column A on a sheet and I'm trying to write a formula on another sheet that will check all cells on a pre-populated row and return a value if any of the words from the list on are contained in any of the cells in the row. I've attached an example spreadsheet
An original text string entry appearing in an Excel cell would be:
"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"
I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.
Another example would be (very different from the first):
"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"
You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.
The above list represents 2 columns. The left column is 400 lines long. The right column is partially complete with 2 letter codes that represent the 6 letter codes on the right. I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text? I don't want to have to manually type in 2 letter codes that match up the left column. What do I do instead?
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
I'm copying data from another source to excel, and for certain date entries they come as text in the following format "On 14 May at 8:00". Is there anyway to convert multiple entries like this into a standard date format? With or without time is fine - just the date will do.
I have a long date and time text value in a cell such as "2013/10/02 07:43:39.39", where the fractions of a second are very important.
I can use cdate([text]) to convert the text string to a value, but only if i omit the seconds fraction: "2013/10/02 07:43:39". Otherwise i get a type mismatch error.
Is there any way i can get the whole date/time string converted to a date value? The only way i can think to do it it at the moment is to convert the date/time, then divide the seconds fraction by 86400 and add the 2 values. Is there a better way?
Secondly, i have a large number of these date strings, typically >30k lines x 7 columns. Each string is preceded and trailed by a [space] character.
I can strip these extra spaces no problem. And, i can "loop" to perform it, again no problem. But it's going to take time to perform and i'd rather not if i can avoid it.
Is there a work around to pull data from cells in excel to another tab with standard language? For example the template language will stay the same except in example A's case the last sentence "0.0" hrs will vary based what's sitting in B34.
It's stating the formula is too long. Is there too much text?
Sometimes the link would be in the middle of the text as shown in example B below with dates 00/00/00.
EXAMPLE A For any leave of absence that is granted for more than 10 days, time off is governed under the Disability Bank Policy. Under the Disability Bank Policy there is a 10 day elimination period which must be satisfied prior to gaining access to your disability bank. During this 10-day elimination period, you are required to use your sick time for the first 6 days (48.00 hours) and vacation time for the remaining 4 days (32.00 hours). After this elimination period, we use all the time accrued in your disability bank. Currently, you have a disability bank balance of "00" hours.
EXAMPLE B After this disability bank is exhausted, you will use all remaining and future paid time off balances from your vacation/CME/holiday banks. Because your leave begins in 00/00/00 and ends in 00/00/00 we will advance you future paid time off balance thru 00/00/00. Once those balances are exhausted the remainder of the leave will be unpaid. Per our policy, should you not return to work, you agree to pay back any time advanced to you within 30 days of your separation from the company.
I have a range of numbers stored as text. I need to convert them to long numbers using vba. I tried typing a simple number = clng(a2), but that didnt work. Can someone help me with A) the right formula and B) the proper end(xlUp) format.
I m trying to compute the mean and standard deviation for the following output:
Private Sub RunMCSimButton_Click() 'Sub created so that once the macro button is pushed, it outputs a specified number 'of FTSE 100 values and the equivalent binary payoffs.
Dim Kt As Double Dim q As Double Dim r As Double Dim IndexBetValue As Double Dim SampleSz As Long
Dim IndexSim() As Double Dim Cnt As Long
Dim Payoff() As Double
'All the relevant variables have been defined.
'Read the parameter values from the required cells. With Me.Range("d4") Kt = .Cells(1, 1).Value q = .Cells(2, 1).Value r = .Cells(7, 1).Value...........................................
As you can see from the code, by pressing the macro button a list of FTSE 100 values and binary payoffs are produced and outputted into the spreadsheet. What I need help on is writing a piece of code that, also on the click of the macro, will output the mean and standard deviation of these values in specified cells, e.g. C10 mean and C11 SD.
I have an issue with long numbers (16 digits). To display them correctly in excel I formatted the cells as text. This part works fine, but now for some reason conditional formatting marks different numbers as duplicates.
In my case 3081281170122602 and 3081281170122601 are considered as duplicates by excel.
Attached is the mock excel spreadsheet. I want to read "sam" from the lookup column's long text "sam is good" and then write "4" in the next column. Similarly read "white" from the long text "white is tired" and write "1". And so on,,,,, For more criteria, see box highlighted in yellow,,,,I used VLOOKUP but what am able to make it work only when there is one word "sam" in the lookup column. It returns #NA when the text is "sam is good". It should write nothing if none of the criteria is met and should keep doing until the last cell in the lookup column.
I have a spreadsheet that has a column of text that is always 10 characters long. There are 10 rows of text so there could be 100 text characters if all rows are filled. The rows usually will not all be filled. There will probably be blank rows between used rows. I have been able to capture the text and put it into one cell as one long text which is ok but I want to be able to break it up into the 10 character strings again, separated with a comma and space between each 10 characters.
This is the code I used to collect the 10 character text strings and put them all together as the variable "result". I used & ", " after ... Cells(r,17) which worked fine unless there was a blank row. If the row was blank it put in a comma and space anyway so I ended up with duplicate(triplicate) commas and spaces.
Private Sub test_Click() result = "" For r = 5 To 32 Step 3 'If Cells(r, 17) "" Then result = result & Cells(r, 17) Next r Range("r5") = result End Sub
I have a spreadsheet of barcodes 114,618 rows long. I have a program that requires me to enter in each and every barcode in a certain format. This is the format that it requires me to place the barcodes into.
Now I have a Macro to add a comma to the end of these barcodes. That part was a breeze, however, I have hit a brick wall when it comes to placing them into an ongoing sentence type format, that my program needs. I have found several ways to combine and merge and "justify" the content, however these typically only cover small amounts of content, and doesn't even come close to hitting anything over 100,000. I don't know if maybe I need to be using another program in tandem with Excel.