I have a spreadsheet with thousands of dates in this format:
Tuesday 16 September 11am - 4.30pm
What I need to do is split it out so that I end up with a cell with
16.11.08
and another cell with the time string.
I am not sure the best way to go as the day part of the string will be different lengths, as will the other parts, plus I will need to add the year in.
in column A, I have a date like 11.15.2006 .I want to write 11 to column B, 15 to column C and 2006 to column D. How can I manage with VBA?
View 3 Replies View RelatedI have a table column I has format tues 12/04/2014 ect,when I filter to put date in order it seems to use day first and messes the dates up how can I remove day ,tues ect into another column,i get table sent to me so can not tell them to do at source.
View 5 Replies View RelatedI need to split the = Now () in Cell Range F1 ( custom format is ddmmyyy) then seperate or split each so,
in Cell Range H1 is dd
in Cell Range I1 is mm
in Cell range J1 is yyyyy
Is this a formula or a macro function ?
I was able to use the information contained, therefore a similar question I had
was not required to be posted twice. This place ROCKS just by reading other posts.
I'm trying to split an amount within a given month:
Customer
From
To
Total number booked
Total amount booked
A
3 March 2013
5 May 2013
458
£5,000
Therefore, assuming that the number and amount booked is done evenly on a daily basis and considering the fact that in March, the period is 3 March - 31 March, while in May it is 1 May - 5 May, is it possible to have a formula that calculates the amount by month?
I have been using =D2/(DAYS360(B2,C2)) to find out the daily number booked, then multiplying this by the days in a given month to find out the monthly value, but is there a more elegant way of doing this where I can define the month in another sheet (Col. B), to return the following in Col. C and D?
Customer
Month
Monthly number booked
Monthly amount booked
A
March 2013
207
£2,258.06
A
April 2013
214
£2,338.71
A
May 2013
30
£322.58
I have a spreadsheet with every day of the month where I input quantities of purchased product. We divide those quantities according to the number of days of pricing period.
So if we purchase volume of 100 on the 1st of the month on a 10 days pricing, I would like to have in my spreasheet a volume of 10 for each 10 first days. Easy so far, where I block is when I start having several deals, at different days and with different pricing period.
See attached file for example.
How can I increment my column? Would it be Macro or can I just stick to formulas?
After I imported this data, the date and time is in the same column in the format of "mm/dd/yyyy hh:mm:ss" military time. How do I write a VBA code to split up the date and time into two separate columns. One column would only have "mm/dd/yyyy" while the other only have "hh:mm:ss" in military time.
Eventually, I need to extract information from the data by looking for a specific time. I would also plot time vs something.
I don't know if treating it as a string would work, because it would just become a text rather than a time, right?
In Column A, I have a date formatted as follows: 10/13/2007
I want to break this out into three other columns as follows:
Column B Month = 10
Column B Day = 13
Column B Year = 2007
I need all three columns to be values that I can use in a VLOOKUP.
We are an office furniture company and we want to hold a master list of all stock. The columns we require are:
Product ID
Product Description
Product Grade
Product Price
Sold
The Sold column would be a YES or NO answer, and based on that I want the data to split into two additional worksheets, one with all the unsold items and one with all the sold items. So really I need to know how to split the data based on whether the line has a YES or a NO in the sold column.
The data needs to update instantly so if I change a product from unsold to sold it then comes off the current available stock tab.
I just want a macro to split the hours to a date and time in column A. I just preferred macro rather than a formula.
Ex: [Code] .......
Column B1 should be: [Code] ........
I've also attached the file : hours split.xlsm
splitting out date/time strings to separate cells, but I cannot find how to split the following.
I have the serviceable date of an aircraft delivery set as 'S hh:mm dd-mmm-yy', which is due to being used by an external application in this format. This is entered by the user and I need to be able to extract the date time strings and switch them to dd-mmm-yy hh:mm, excluding the S from the result as a formula in another cell.
I keep getting a #value error or returning the whole string result.
I have contarct for amount $5000 with a start date of 1/1/2014 - 3/31/2014 (3 months). I would like to equally split my amount based on my number of months between 1/1/2014 and 3/31/2014 ie $1666.66/per month.
View 4 Replies View RelatedI have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. Any way to identify the number of days, per calender month, that falls in a date range.
sample data...
Start Date
End Date
Old Value
New Value
08/03/2010
18/06/2010
16758.2
16758.1
[Code] .......
I need to break down the total number of days per month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
24
30
31
18
[Code] ......
As you can see this also laps into a new year, which poses my next problem, ill probably just add more columns on to the end of the table for that though...
I will later apply different calculations to these cells but in short need to get a calculation for the number of days per month first.
(in short spreading the new value out accross the year then multiplying it by the days... i also need to apply a further daily volume cal to it).
I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Part 1:
#########################################
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".
I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.
So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
#########################################
Part 2:
####################################################
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"
There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.
I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.
So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".
####################################################
How do I split numbers!
I have two problems/challenges!
Part I...
I got the answer 1987, and now I want Excel to take the numbers out and display...
1 in one box then i set + in the next, then 9 in the 3ed. box, next box +, then 8, then +, then 7 in the last so that i can have Excel make a SUM of it all to 25.
How do I split 1987 and put the numbers in different boxes?
Part II...
I want to make A=1 B=2... all the way up to 9, then start over again with J=1 K=2... up to 9 again and then over again.
So that if I write my name it comes out as a value of 14 (Odd = O=6 D=4 D=4 =14)
(AJSØ=1 BKTÅ=2 CLU=3 DMV=4 ENW=5 FOX=6 GPY=7 HQZ=8 IRÆ=9, It's the Norwegian alphabet, that's why there are some extra letters)
So how do I set up my Excel so that is ANY name is typed in I can get it out into a number from the values assign?
I have one column with as many as 50,000 or more rows. The data format for each row/ cell is unique as shown below ( date and time). I wanted to split the data as shown in "Formatted Data" below. Have Tried Text To Column formatting but didn't work right.
Raw Data: Formatted Data (2 cells):
2005/11/02 23:55:15.758 ==> 2005/11/02 23:55:15.758
2005/11/02 23:58:16.698 ==> 2005/11/02 23:58:16.698
2005/11/03 00:07:13.830
2005/11/03 00:10:14.971
Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g:
A2: aa,ab,ac
String Value
aa 1
ab 1
ac 3
What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.
I have a table like this:
QTY PRODUCT
---------------------
1 AB111
3 CC332
2 DA221
Is there a way to display that information on another sheet so that each individual product is on its own line with a QTY of one? I can do it with VBA but was wondering if there is an alternative?
QTY PRODUCT
---------------------
1 AB111
1 CC332
1 CC332
1 CC332
1 DA221
1 DA221
I was multiplying 2 ranges in an formula to get a cell's value. The first was a set of user inputs and the second was an array of constants. Recently I needed to add another field in the middle of the user inputs which subsequently broke the range.
View 4 Replies View RelatedI have in one column first name space and then surname. I want to divide it into two columns 1 for the first name and 2 for the surname.
View 2 Replies View RelatedI have a very long HTML code that i am using for a description of something on our website, when i save the file in CSV format the HTML gets cut up and put into different cells on different lines ... the file needs to be saved in CSV format.
View 1 Replies View RelatedI need a routine that splits a cell with a name into its various components.
For example: split the name Johnny Jones and place Johnny into a different cell, and place Jones into yet another cell.
QB03654338AGLQLD210/06/2007 0:0014/02/2008 0:00
QB03654338AGLQLD215/02/2008 0:0010/04/2008 0:00
QB03654338AGLQLD219/08/2008 0:0031/12/9999 0:00
to this:
QB03654338AGLQLD210/06/2007 0:0014/02/2008 0:0015/02/2008 0:0010/04/2008 0:0019/08/2008 0:0031/12/9999 0:00
So adding it to all to one line if it is the same ID number "QB03654338". See the attached file.
In column A of a spread sheet I have lots of entries in this format:
admin_staff_a_002
admin_staff_a_006
admin_staff_1_010
admin_staff_1_003
admin_staff_b_005
admin_staff_z_007
admin_staff_z_001
What I need to achieve is to have a different worksheet containing all the rows for admin_staff_a then another for admin_staff_1 etc.
I want to name each of the worksheets a, 1 etc so i'd remove the admin_staff bit and the _003 bit
One of my three active brain cells isnt functioning today, so I need some help.
I am trying to use the "split" function but cant seem to get it working.
The variable sWbName is "MOT 4506667 C 142 #JN #SN.xls".
So I have strings like the one below. What I'm looking for the text behind the ":" being, 17-SEP-2009, 000737-EWO, SRS and finally 002-YAO
Example string:
date: 17-SEP-2009, number:000737-EWO, detail: SRS, DD:002-YAO
My ...
One of the lines in my VBA code reads as follows
View 4 Replies View RelatedI have a list of fixtures but each cell lists the teams in each game ... i.e.
CSKA Moscow v Real Madrid (in one cell)
List example....
CSKA Moscow v Real Madrid (Cell 1)
Leicester v Portsmouth
Leyton Orient v Charlton
Liverpool v Newcastle
Lyon v Apoel Nicosia
Man City v Liverpool
I want to split these cells so CSKA Moscow is in cell B1 and.... Real Madrid is in cell C1 etc.
I have long list of data (asett IDs). All of it is in the following format.
AA-BB-CC-CC001
The number of dash occurrences can vary based on the level the asett is located in. I need to extract the parrent of the asett to adjacent column. the parrent for the above example would be:
AA-BB-CC
The parrent is the values before the last "-". There can be as many as 10 "-"s and as little as 1 in the asett ID.
I have been looking at the split and trim functions in excel but I have not been able to define split/trim by last "-".
I am putting together a macro that takes hours worth of work away by doing all the calculations and splitting.
I have managed to do all the basic stuff but this is a little more complicated.
My spreadsheet has a couple of thousand rows of data, i need the macro to check for the following:-
If DATA14 (Column N) = YES and RATE (Column D) = 21.81
I need this line splitting into two to replace the original. (would be better if i could move the original data to another sheet to show the ones that have been split)
One with 87% of the rate
One with 13% of the rate (Data8 will always = OLHA on this row)
Then the total will be recalculated based on the new rate.