I need to convert my percentage marks into values based on competency values. Once again my school board has change the final reporting method. Last year we had to use A, B, C, D. This year it is based on scales of 1- to 5. Since excel cannot have more than 7 nested IF statements I was wondering if anyone knew of a way to have a percentage mark converted into a scale number. Below is the following data.
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 have the following text in B5 "deposit fee 0.75%". I have a value of 3500 in B2. I would like a formula where a can multiply the value in B2 x the 0.75% that is contained in the text "deposit fee 0.75%"
I tried to use =B2*VALUE(MID(B5,17,1)), but it gives me #value
I have an excel file that contains data imported from a csv file. One of the columns contains text strings that I would like to convert to a different text string. The column that I want to convert has text data that is similar to the following format:
BL BLDO BLDO BLDO BLDO EL BLDO
Now, the data above should look like this:
+CONC CONC CONC CONC -CONC
The "+" should replace the "BL" in the first row. The "CONC" should replace the "BLDO" in all rows. The "-" should replace the "EL" in the the bottom row. Within these files that I work with, there are perhaps 60 or 70 unique text items that each have a corresponding text item that should replace it, and the files usually contain upwards of several hundred to one thousand rows of data. In other words, I have a list of 60 or 70 text strings that have a specific replacement text string...and I need to apply that conversion to several files each day.
I am currently trying to find an easy way to convert cell references to number format. The issue I'm having is when compiling a small formula (CONCATENATE) as part of a macro in order to produce a unique reference number to a column of data (1000's of cells long) I come accross the age old number stored as text issue after the macro has converted the formula to values.
Have tried editing the macro to number stored as text = false which removes the annoying little red triangle but the vlookup functions still wont read the values. My current solution is to highlight the entirity of the data and convert to number which takes a long long time as there are multiple lookups assigned to the cells, which for my current project is not acceptable.
Am looking for a quick solution, (with no interaction required) something that would convert the cells to the format required either before the formula is assigned and overwritten by values or just after. Currently I'm stumped.
I need to build excel sheets and be able to convert images to text. I copy the sheet from a web site and all fields in the table come in as text except for one. That field can be 1 of two images. Right now i have to save the xl sheet as html and then Find/Replace the html image code with a + or - sign which is time consuming. Is there a way I can convert he image though excel. I need to be able to sort the sheet as well as pull the data into a separate tab.
I am receiving a monthly breakdown of postage costs by department but the values are received in a text format of 1 234,56.
I need to convert these to numbers.
I have replaced the comma with a full and tried to use =substitute(cell ref," ","") to remove the spaces in those numbers exceeding £1000 but this does not seem to work.
I have a formula which extracts a number from a text string.
The text string is in A2 - the formula which extracts the number is in B2
However - when I copy the data in B2 and paste values to another sheet / cell - the forumla is as text - and I get the little green corner symbol and I have to convert to number.
Some VBA code - that will automatically convert my extracted "number" - to an actual number in number format.
I have imported data that is showing dates as 18th Sep 2013. Excel does not like this and I have tried various methods of conversion but nothing seems to work. I will be getting data that has Oct, Nov, Dec etc so the formula needs to be able to handle these too. I would like these 'dates' converted to excel friendly dates.
i am having a problem here,i Have got two columns one for item name other for item quantity.the quantity field has data like: 2+2+8 5+5 2+9 5 666+98 258 6+89+7+4+2+4+5 .....etc.
i need to calculate the total of all the cells like the first has sum 12,second has 10 ,fourth has 5 .Now i can get the sum by simply putting a " = " in the start of the cell but the problem is that ive got 8000+ cells putting = manually will kill me.
On my Excel spreadsheet, I have a TEXT value in A1 that is all numbers, i.e. "000065200". I would like to write some VBA code that changes A1 to the numeric value . How do I do this?
I have few dates in column C. My Column B is blank. I want to convert all text in column C in proper date format in Column B.
The dates entered in column C is in this format 05.11.2007 (dd.mm.yy format) Is there any formula to convert it in proper dates in same which will show the date in 5-Nov-2007 format?
I have a column (R) that is formatted as text. Each cell represents a time in military format.
How do I convert the values in the column to be in 00:00 (hours:minutes) format instead of text?
For instance, 1200 is the value in R2. I want it to show as 12:00.
I tried the following:
1. Create a new column (S) 2. Format the new column (S) as 00:00 format 3. Copy cell R2 (whose value is 1200) and paste into S2.
Result: S2 value is the same (still 1200). Formatting in the S2 cell changed from 00:00 format BACK to text format upon me pasting.
So I tried and changed it BACK to 00:00 format, and I get 0:00 as the new value in cell S2. Also, the function bar shows "4/14/1903 12:00:00 AM" for cell S2. I need a formula for this apparently,
on my Excel spreadsheet, I have a TEXT value in A1 that is all numbers, i.e. "000065200". I would like to write some VBA code that changes A1 to the numeric value .
I need to convert some text to a number. I was wondering if there is a way to do it in Excel. Like, for example, the date 3/14/2006 can be converted to 38790. Is there any function in Excel that can convert a word to a number?
Example would be (numbers are totally random)
md_bla_bla_02 would convert to something like 3452615261502
I have an excel work book where in one page (sheet1) contains formula types to be used based on a code. and in the other page (Sheet 2) when I enter the code I need it to get the formula from 1st page and then do the calculation in the second based on the code. Ex : Sheet 1
Code Formula 0 Cell A * CellB 1 Cell B - Cell A*5% 2 (Cell A + Cell B)*10%
I hope you got the picture. The problem is I'm not sure how to take only the formula from 1st sheet and then use it in the 2nd sheet to do the calculation. I wont mind using a form for this even.
I have five columns on an Excel Spreadsheet with questions in each of the columns.
Below each question is a number value. In the rows below these number values the users will be answering the questions with Y or N. When the spreadsheet is complete I would like to replace the Y's with the number value shown under the question and replace the N's with a number value of zero (0).
I have attached an excel sheet and I want to count and report frequencies the amount of X that are occurring from the 'CSR1' book in a table in the 'Frequency' book.
The CSR1 book has 4 sections and I need to formula to adjust the total percentage accordingly if only 1, 2, 3 or 4 sections have been populated with data.
I have a problem in converting text to columns for a string that has 1000+ entry's. Excel bumps me out after 851 using the Split function.
The data I can only download in a text format has >1000 fields that are seperated by ",". Not a problem so far but when I attempt to convert to columns there is a maximum limitation of 851.
My boss sends me emails with people's information, which I then reformat to put in invoices in word, save as PDF, record in excel, and send out with an email from Outlook. I would love to find an easier way. I'm wondering about putting the info in an excel table so that I can then use a mail merge to create an invoice with the address--unless it turns out to be more work than to format each one (I'm newish to Excel).
The info I get is listed in the following way:
First Name: Mia Last Name: Kant Email Address: mika@gmail.com Country: Afghanistan
[code]....
I need to use some but not all of the info, but if I could get it to format into a table, I assume I could then pick and choose.
I am trying to convert text in a cell from: 03/08/2014 2330 to a date that looks like this: 03/08/2014 23:30. Then I also need to extract just the time only and put that in a new cell.
I can do it in a few steps by using Data -> Text To Columns, formatting the date and time separately, and then putting them back together again.
Is there a 1-step way to do the original text to date/time conversion?
I have received a large file of text and numbers arranged (loosely) in columns in a .txt file that I need to get into a usable form in Excel. I'm trying to use the Text Import Wizard but am running into problems geting the column breaks right as there are thousands of rows of data items. As soon I think that I've inserted the break line in the correct place I scroll down a little further to find an item that encroaches into the next column, and when I shift the break line to the right to accommodate this new item I then encroach on the items in this next column.
I have text amount in L55 column. The amount is auto summed amount. Example, $ 5081.16
Now in C59 column (total FOB) I have to write the amount in text manually. Any formula to make the amount converted in text automatically??? I badly need this. Attaching image.
THE Format of text amount has to be like below.
ex: FIVE THOUSAND EIGHTY ONE AND SIXTEEN CENTS ONLY.
It has to be caps locked, there will be and between cents and main amount, there wil be cents after that. Attaching image.
I am pulling out information from a cell using =RIGHT(C2,4). It gives me 6-2 in the cell, which is what I want. Now I want to sum the cell but how to do it. I have tried converting it but it doesn't work, I tried pulling out each digit, but that didn't work.
Ideally, I would like a formula that could convert cell A1 into a result in one cell.
