Split Cell Reference In Half

Jan 8, 2013

I have two sheets. One containing in cell B2 "1211 - Extra Core Hours" (t_Cost_Centre_data) and then another sheet that displays the data. On the display sheet, there need to be two fields. Event ID and Event Name. The event ID is the "1211" and the name "Extra Core Hours".

My question is, how do I split the reference so that only the ID and name appear in the relevant cell?

Ie Event ID Event Name
1211 Extra Core Hours

Note, the ID's and names will change and they will be multiple.

Formula Help!.xlsx

View 3 Replies


ADVERTISEMENT

Split A Cell In Half

Jul 29, 2008

Is there any way I can split a cell in half, so I can put numbers in the left part of the cell, and the right part?

If so, I would I refer to those numbers in my macro?

View 9 Replies View Related

Split A Column Into 2 - Use Character Limit That Does Not Cut Words In Half

Jun 11, 2013

I have huge columns of data that can only be 30 characters long. I want to use a formula to split the column into two. The original column will have a character limit of 30, and should not cut words in half. If the Length will be more than 30 while including the entire last word of the cell, that word should be the first displayed in adjacent cell in the 2nd column.

The 2nd column can be any length.

I usually use a Text to Columns method, but dont know how to make it not cut off the words.

View 9 Replies View Related

Excel 2010 :: How To Merge Big Dataset That Is Split In Half

Mar 9, 2014

I am running 2 audits on aspects of patient care. The first audit records a unique number that identifies the patient, and then a series of answers on demographics, and other stuff.

The second audit also records the unique number, and collects some other data on the particular patient at a later point in time (medication usage, levels of pain etc).

So in theory both audits will collect different information on the same patients. In practice, some patients will be missed and there won't be matching data sets. The order of collection won't be the same either, ie Audit 1 might be in the order of Patient 1,2,3,4 etc but Audit 2 might be patient 2,4,1,3

For various reasons these two data collection tools are not linked, and I end up with a spreadsheet for audit 1 and a spreadsheet for audit 2.

I need to merge these so that I can see all of the data for a particular patient at a glance, and where the gaps are, and apply some statistics to it etc. I could sort both lists by the unique audit number so that they are in order, and then copy blocks of data over from one sheet to the next, but there will be records missing, i might make a mistake with the alignment, and I'm sure there must be a better way.

I am using Excel 2010. Each audit case has about 50 columns of data for Audit 1 and 30 columns for Audit 2 . There will be ~20 new records (Rows) created each week that I want to progressively merge.

View 1 Replies View Related

Excel 2010 :: Cut Out Half Of Data From A Cell

Sep 11, 2013

Is there a way in excel 2010 to cut out half of the data in a cell .. to make the top four in example to look like the bottom four?

1986 Jun- 9 to Jun-13

1986 Jun- 2 to Jun- 6

1986 May-26 to May-30

1986 May-19 to May-23

1986 May-12

1986 May- 5

1986 Apr-28

1986 Apr-21

View 5 Replies View Related

Macro To Format Half Of The Cell With A Different Color

Nov 28, 2008

I have a list of records that will eventually be used to create a pivot table. Sometimes an account number will be listed twice but instead of adding to the previous info, it needs to start a new record. I decided in these instances to mark the account number with a character (+). Even though the + will designate a separate record, I'd like it to be invisible.

Is there a way to automatically turn the font color white for only the +? Since I've automated the rest of the process using a macro, a VBA solution is fine.

View 9 Replies View Related

Automatically Changing Color Of Cell Within Half An Hour Of Inputting

Jan 9, 2013

I want to create a macro or formula for changing the colour of the cell after half an hour of inputting.

View 3 Replies View Related

Macro Which Run After Every Half Second

May 16, 2014

Macro which run after every Half (1/2) Seconds..

View 2 Replies View Related

Split Data In A Cell And Get The Value For The Split Data From A Different Table

Jul 31, 2006

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.

View 9 Replies View Related

1/2 As In A Half But Its Coming Up With The Date

Apr 10, 2009

When I enter 1/2 I want it to stay as a half but when I press enter it automatically changes to the date. How can I stop this?

View 2 Replies View Related

Pause For 0.5 Seconds (half A Second)

Jun 3, 2009

I need to pause for 0.5 seconds (half a second). How can I do that. applicatiom.Wait can only be used for whole seconds and a FOR ... NEXT loop is CPU dependant.

View 3 Replies View Related

Round To Integer Of Half?

Feb 5, 2012

I would like to round down to interger or half. I would give you some examples:

3.76 to become 3.5
2.48 to become 2
-1.12 to become -1.5
0.05 to become 0

I tried to use round down fucntion but I am not able to have the desired outcome. I thnink the issue is with the second argument of the aforementioned function. How many digits should be indicated? I put 1 but it does not work.

View 9 Replies View Related

Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.

________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)

If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?

View 4 Replies View Related

Vlookup Only Working Half Way Down Spreadsheet?

Jan 20, 2009

I have a spreadsheet with members details in and I've added a few columns showing:

Col Q: Paid 2008 (blank if No, 1 if Yes)
Col R: Show Paid Yes or No (shows a Yes or a No)
Col S: Date Joined (only entered if new and joined during 2008)
Col Z: Subs owing from 2008 (if existing members and owe last yr's subs)
Col AA: Subs to Pay in 2009 (a standard £35 or pro rata if joined during 2008)
Col AB: Pro Rata (if they joined other than at the beginning of 2008)
Col AC: EA Sub? (based on a yes/no column, to add £5 if yes)
Col AD: Total to Pay for 2009

So the formulae (or data entered) for the above columns are as follows:

Col Q: Paid 2008 - blank if no, 1 if Yes
Col R: Show Paid Yes or No - =IF(Q2=1,"Yes","No")
Col S: Date Joined - a date entered in format d/m/yy
Col Z: Subs owing from 2008 - =IF(R2="no", 35,0)....I want a default 35 in here
Col AA: Subs to Pay in 2009 - =IF(S2="",35,"Pro Rata")....says that if there is no date which indicates they are an existing member, they will pay the standard 35, otherwise they'll be paying a pro rata fee...................

Problem:
It all works fine until a certain row (27 actually) - then I get a VALUE! error which points to the Date Joined field. Now I've tested that value against the vlookup table and it returns the correct data, so why doesn't it do it in my spreadsheet????? I've tried changing the formats on the cells; clearing all data; entering a different date, but from that row in the spreadsheet down to the last row, it simply won't work! I've looked at each formula in each cell on the rows where it is working and the rows where it isn't and I can't see anything different.

What else can I look for? It is SO frustrating as it does what I need it to to do but only for a third of the spreadsheet!!!!

View 3 Replies View Related

Convert Date To Half Year

Sep 18, 2012

Trying to take for example, 9/25/2012 and write a formula to make the resulting cell read 2H2012. Or 4/15/2012 and have the resulting cell read 1H2012.

View 5 Replies View Related

Printing Cuts Off Half Page?

Jul 5, 2014

When I go to print my 1 page document, I've selected to have the page shrink to fit onto 1 page. For some reason, when I choose this option to shrink, it doesn't print the entire document, only about 3/4 of the page or so. Something I did recently that was a little different that may have caused this but am honestly not sure is i added my company logo to the header so it'd print on the document too. If I added it as an image, for some reason it wouldn't print.

View 7 Replies View Related

Half Of Chart Column Is Not Visible

Jul 22, 2007

I have created a combination chart comprising a column and a line. The columns to the extreme right and left are half visible (I can only see half of the column).

Look at my two attachments to see what i mean.
Attachments:
Excel File: http://www.MegaShare.com/235337
Screenshot:
OR
http://img530.imageshack.us/my.php?i...icture1qi7.png

Note: I have cross-posted this thread in excelforum.com: http://www.excelforum.com/showthread.php?t=608124

View 4 Replies View Related

Half Hour Data Into Hourly Values?

Mar 26, 2013

I have large data sets with half hour values that I need to average into hourly values. Here is an example of the data:

28.12.2012
01:30:00
0,1
2,4
2,5
127,6

28.12.2012
02:00:00
0
0,9
0,9
118,5

[code]....

View 5 Replies View Related

Calculating Full-width Half-max (fwhm)

Jun 27, 2007

calculate the full-width half-max of a peak in data? My chart (gray value vs. pixels) essentially looks like a letter M. What I've tried is finding the max of each peak, dividing that by two, and then using vlookup to find the corresponding pixel values on each side of the peak. But, since vlookup does not work for descending values, I cannot find the half-max value on the right side of each peak.

View 2 Replies View Related

Aggregating Timestamped Data Up To Half Hourly Totals

Feb 21, 2013

I have a worksheet containing one timestamp column and a single column of data, the interval being one minute. How can I aggregate it to get half-hourly totals?

View 4 Replies View Related

Determine Half-Way Point Of Bar Series On Bar Chart/Graph

May 30, 2008

i have created a xlColumnStacked chart using VBA in excel 2000. i would like to place a line in the center of one of the Points(). How can i locate a specific point, for example Points(1), so what i can then "draw" a line in the center

View 7 Replies View Related

Split 1st & Last Names & Split Addresses After 1st Comma

Mar 6, 2008

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".
####################################################

View 6 Replies View Related

Absolute Reference (cell Reference Behind The Table)

Mar 11, 2009

I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:

View 2 Replies View Related

Cell Reference :: Formula To Reference New Cells

Feb 15, 2010

I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.

EXAMPLE:

12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

View 14 Replies View Related

Convert Cell Reference To Worksheet Reference

Jul 20, 2014

I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:

Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say

AAB
ABC
CDE

And have a formula in column B that converts this to

=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)

I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?

View 2 Replies View Related

Reference To Cell Column Letter By Reference To Another Cell Value

Apr 25, 2014

Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.

View 9 Replies View Related

Split Numbers And Split Words

May 8, 2009

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?

View 14 Replies View Related

Cell Getting Split Up

Feb 3, 2014

I 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 Related

Split Cell Into Two

Dec 27, 2011

I 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.

View 5 Replies View Related

Split Cell Into 2

Sep 17, 2009

I have a column with info like

name - name

as in (column a): frank - john

I want to split that into 2 cells(columns) and drop the -, what is the best way ?

so now column b would have frank and c would have john.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved