I have to format huge text file to columns. Here is the file: [URL] The whole file should look like the first 15,16 rows. What is the fastest way that can be done? Here is the original file: [URL]
View 3 RepliesI've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:
hh: | mm: | ss
I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM
I have prapared a formula for B2,B3 and C2,C3 and the reult came in B10,B11,C10,C11 and D10,D11,E10,E11.
From that relust i want to get the final result should be as follows.
B10 should again divided in to anil(in one cell) and 16/12/2013 11:33:50 (in another cell). This formula to be added in B10,B11,C10,C11 and D10,D11,E10,E11 formula.
I loooking for the final reslt, i have explain the in the excel. I reuired the final result with single formula from B2,B3 and C2,C3.
I have a text file that I need to open in MS Excel 2007. The file contains the following data. (Each column is delimited by the "|" character.)
Part #|Inventory ID
1743|213,221
1864|10,40
1948|1170,1180
5265|100,104,107,10004
Ultimately, I need three columns but the data needs to look the same as it did before I opened the file.
When I open the file for the first time, I use “Text to Columns” to delimit the fields by the "|" character. The problem is that the Inventory IDs in the middle column get a bit whacky.
Here's what I'm left with:
Part # Inventory ID
1743 213,221
1864 10,40
1948 11,701,180
5265 10,010,410,710,004
The comma is retained appropriately when there are two 2- or 3-digit Inventory IDs.
When a row has multiple, longer Inventory IDs or Inventory IDs that vary in length, the comma is moved to every 3rd decimal place.
I tried putting quotation marks around the data before I opened the text file for the first time but that didn’t do anything. I tried Custom Formatting to retain the comma position (0000,0000) but this only worked when I had two 4 digit IDs. I also changed the number type to Text and General. Once the comma was changed, I couldn’t figure out how to get it back.
How to best retain the format of my Inventory IDs.
My data is in column A. I need to have the data as in column E and F. Column E is the field names.Rows can be up to anywhere and may very.
Excel 2007
A
B
C
D
E
F
I have a column "g" with this conditional formatting:- =A2<>A3 Format Bottom Border.
However I will pass this workbook onto someone else who will fill in the text in column "g". They will use copy/paste text from other cells or columns even other workbooks that will not have the conditional formatting.
I have used Cells > Projection > Locked unchecked then used Tools > Protection > Protect Sheet and checked all. There does not seem to be a way to unlock the cell but protect Conditional formatting. Each time I copy and paste from other non formatted cells it wipes out my formatting.
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))
I have a workbook with numerous worksheets that contain text, steps actually. I would like to select all common words and change their formatting to bold. Every time I try however, it formats the entire cell in bold. Is there a way to isolate just the word "click" for instance in all the cells in each worksheet and change the formatting to bold, and leave the other text within the cell normal?
View 9 Replies View Relatedhow to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.
I'm using Win 8.1, Office 2013.
I true Excel novice here-
We have a BUNCH of data we need to get in to a spreadsheet format-
can anyone kindly tell me how to do the following
985-0050
3PAR ADVANTAGE, PRICE PER NODE FOR 4, 6,
3PAR ADVANTAGE, PRICE PER NODE FOR 4, 6, AND 8 NODES
$14,876
we want the data to go from 4 seperate rows to four new columns
PART NUMBER, DESC1, DESC2, PRICE
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies View RelatedI have the cell data as below
How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?
I still want the original data as I need to check that the splits worked well?
16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5
I'm trying to move multiple ROWS to COLUMNS. My data looks like this:
ID CLASS_NBR1 UNITS_TAKENGRADECLASS_NBR2UNITS_TAKENGRADE CLASS_NBR3
000555 688 1 A
000555 567 3 B
000555 888 1 C
000555 999 1 B
000555 889 3 C
101999 889 3 A
101999 567 1 B
I have between 4-9 rows that I'm trying to move to columns. I'm attempting to move data for each student on one row, currently each student has between 4-9 rows.
I've tried so many different formulas and MACROS that didn't work I started doing this one by one (manually) but I have too many rows and it's taking too long.
I wish to have one conditional format apply to K and L. Presently it's L only where it works.
=OR(K1="",AND(K1="No",L1<>""),AND(K1="OJ",L1=""))
Why when I change the range from =$L:$L to =$K:$L does it not apply to both?
I have a conditional formatting issue, using VBA.
My conditional formatting code works fine for one column; however, when I try to apply the conditional formatting to two columns, the code only applies to the first column listed. Additionally, I have two sets of "rules" that apply to the same column. One of the "rules" includes two columns. The other rule applies to only one column. I'm not sure if this is my issue, or whether I'm trying to apply the same code to two different columns.
My code is below.
Code that applies to column "M" only...
I have ammended the colours to 4 colours that I need for a risk assessment tool . I have added a second worksheet and put a dummy sample of my table there. Can you help me with how to put two colums of conditional formatting in the sheet? The risk rating before and that after the risk treatment? Many thanks in anticipation. I have highlighted in yellow the columns where the product of the consequence and likelihood is likely to be and this is where the colour coding needs to be applied. if you can help with putting the solution in the spread sheet that would be a great help. Any reply via this forum or via my email would be greatly appreciated.
I am new and I hope I have not made a mistake in starting a new thread. Sorry if I have but I wanted to bring this up as a new post so I get a sooner response.
Is it possible to use conditional formatting to set all the unused columns (S - ZZ) interior color to gray?
View 5 Replies View RelatedTrying to create a button that, once clicked performs the following task:
Checks whether the text in a cell in the column X2:X40 matches the text in a cell in the Range A2:U14.
If it does, the formatting of the cell in X2:X40 (eg background) must change to match that of the corresponding cell in A2:U14.
Only 1 cell in the range will match 1 cell in the column
For example:
Before click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a clear background
During click:
matches these cells
changes X4 so that it also has a yellow background
After click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a yellow background
What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells
The data I require is :-
(a) The persons name which is always after the word Requester e.g. Requester Steve Robinson
(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)
(c) The Approving persons name which is preceded by Approved by e.g. Approved by Christine Hunting
See examples 1 & 2 below
Example 1
CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting
Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before
Example 2
CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers
Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.
As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text
I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.
For example
Column A drop down selected is "possible"
then B coulmn should automatically populate "1-3"
C should populate with "3-5"
D should be "5-7"
I am using MS excel 2007.
I have what I think is a pretty complicated issue. At my job, we have a running and constantly changing list of jobs that we have to work on throughout the day. The job management system is really unwieldy and it’s often hard to tell what exactly we have to do today and what can wait until tomorrow. So a few times a day, I export the list to an excel doc, which I’m trying to set up with conditional formatting to highlight as green the stuff for today, ghost back what can wait, and leave white and blank the cells that don’t have any info in them. I but the attachment system wasn't working for me, so I can't provide the example I've been working on.
So far, I’ve managed to set up a formula in the conditional formatting that takes into account column C, but I need to take into account information in columns B, C, and D to determine if the job line should be highlighted or dimmed. Column B is the job's title and it is just text, column C is the Begin date, and column D is the due date. here’s what I need the conditional formatting to do.
If the begin date in column C is Today at 9:00am or earlier, and the due date in column D is today, I need to highlight the column. I managed to figure out the first part of this formula, but don’t know how to keep the line from hilighting if the due date is tomorrow: =$C2<=TODAY()+TIME(9,0,0)
The second part is that I need to hilight jobs that begin and are due today (have today’s date in columns C and D), regardless of what time they have if the task title in column B starts with “Rework Request from PS” or “Rework Request from QA"
Ideally, I’d like to change the text color to something nearly white to sort of hide the jobs that don’t need to be done today, and I figured that part out based on the formula I did manage: =$C2>=TODAY()+TIME(9,0,1)
apply this to the whole sheet so I can paste the report into it a few times a day without having to rewrite the formulas in a new sheet every time, and the number of items does change throughout the day. However when I do, the empty cells turn green as if they’re due today, and I can’t figure out how make them stay blank.
Im having a little trouble with this, Im dealing with a list, and I need to change the colour of rows, based upon a value in 2 different columns.
Im close, but cant quite get it.
Basically its like this, the data in these cells will only ever be either; blank in one or the other, both are never blank. or a number up to about 3 or 4, usually 1 or 2.
Date formatting. I have two columns first column is for the date (3/6/2013) and the second column is for the time (12:37:16 AM). I would like to combine the content of both columns to be like (2/11/2013 12:35:00 AM) .
I am using concatenate but I don't have any luck. How to combine the content of my two columns.
I'd like to compare two columns of data and highlight where a value doesn't occurr in both columns. The problem I have is that one of the columns uses * around the text to ensure it catches all variations of the value.
I have the following formula which would work if the data was exactly the same.
=COUNTIF(lst2,value)=0
However, these *'s are meaning it never works.
Is there a way to make it so that if the values with the *'s are not found in the 1st column of data, conditional formatting is activated?
I have data in cells A2 - N2 and cell O2 is currently blank. I am trying to set conditional formatting where if O2 has a value entered into it, it will conditional format A2 - N2 and highlight the entire row red.
I tried a formula like =O2"" but that does not work. Also tried a few other things but no luck.
Also, once the formula is set, what is the best way to apply the conditional formatting to rows 3 - 30. I tried the format painter tool but that seems to want to extend the formatting range from rows 3-30 and not just per row.
Well I spent some time yesterday trying to figure this one out. It was only last week that I have taught myself how to use VBA in excel. However this looks like a though one.
Simply what i would like to do is highlight a column of a certain date (till a certain row number) based on a list of dates.
eg. Across the top I have a set of dates and on another sheet i have a list of dates.
I understand that there is a VBA code for such conditional formatting but i can't get my head around it.
Instructions are in the attached excel as well. Please see attached
If a person puts in a price in column A, that results in column B "Everyday Price Per Unit" being greater than column C "NDP", then highlight column A cell input red.
=Column B>Column C, then turn input cells in Column A Red.
First four highlighted are examples of thise formula scenario
** All based off an input into column
How to use conditional formatting to do so???
Excel Conditional Formatting 06.24.14.xlsx
conditional formatting in Excel. I have two columns with pertinent information. I need to know the following and format accordingly:
1. Is the number in column A positive or negative?
2. Is the number in column B less than 0.05 or 0.10?
I would then like Column C to just be highlighted a certain color depending on the combination... there are 5 possible combinations and I would like the cells to be formatted so that:
1. Positive and less than 0.05 - Bright yellow
2. Positive and less than 0.10 - Pale yellow
3. Negative and less than 0.05 - Bright green
4. Negative and less than 0.10 - Pale green
5. This "combo" just means the criteria wasn't meant... which is possible b/c sometimes Column A may have text instead of a number of b/c the number in column B is not less than 0.10. If either of these is true, I want the cell to remain blank.
I want to format 3 columns in excel depending on certain value from another column.
By example: I have the columns A B C completed with some text. On the column D it will be the numbers 0 or 1. If the number is 0 the background color from A,B,C columns shall be Green, if the number is 1 the color shall be red.
I have tried in conditional formatting with the formula =$D$1=0 but the color of the columns are changing only depending on the value from cell D1. I don't know what is the correct formula. For column A1 I want to check the value from D1, A2 - D2, A3 - D3,...,An - Dn.
I'm trying to figure out how to copy & paste the following conditional formatting down the column.
The conditional formatting I'm using is in cell A1, D1, G1, J1, M1 (so I have to Ctrl click each cell manually & I then choose to "format the values that rank in the:" "bottom 1" & format it green.
I want to copy and paste that all the way down column A, D, G, J, & M and have the conditial formatting pick for each row, not the whole column. eg. A2, D2, G2, J2, M2... A3, D3, G3, J3, M3...