# Formula - To Obtain The Last Two Numbers Before The Letter B

Jan 25, 2009how it would be possible to obtain the last two numbers before the letter B, if "Fri Aug 01 16:40:49 BST 2008" is placed in A1 so "49" would be placed in B1,

View 9 Replieshow it would be possible to obtain the last two numbers before the letter B, if "Fri Aug 01 16:40:49 BST 2008" is placed in A1 so "49" would be placed in B1,

View 9 RepliesADVERTISEMENT

At any rate, for a statistics experiment, I need a way to obtain precisely 6 random, numeric values in a cell, separated by commas, with no duplicates.

For example, if cell A1 contains the following: 7, 12, 14, 7, 14, 3, then I need to remove the duplicate(s) -- in this case, the extra 7 and the extra 14. After the two duplicates are removed, I then would have to replace with two more values (non-duplicates, of course).

Here are the restrictions, in addition to having no duplicate values appear:

* The first 5 values need to be randomly drawn from values between 1 and 75, inclusive.

* The 6th value needs to be randomly drawn from values between 1 and 15 (inclusive).

* It would be nice - but not necessary - to have the first 5 values sorted in ascending order. For example: 7, 12, 14, 29, 70, 3.

Using the following data

R10-12128

R11-12x12x8

R11-12x12x8

R1-12x12x8

R1-12x12x8

R12-12x12x8

R14-12x12x8

R16-12x12x8

R18-12x12x8

R2-12x12x8

I want it to sort like this:

R1-12x12x8

R1-12x12x8

R2-12x12x8

R10-12128

R11-12x12x8

R11-12x12x8

R12-12x12x8

R14-12x12x8

R16-12x12x8

R18-12x12x8

What is the formula to achieve this?

The portfolio team uses data that is refreshed every Monday. Write a formula to obtain the current week's Monday's date.

View 3 Replies View RelatedI am looking to be able to compare a number with a letter so for example 4c and compare it with another 5c and in that scenario see a difference of 1 but when the two to be compared are 4c and 4a it would see 0.6.

in this excersize

A=0.33333R

B=0.33333R

C=0.33333R

it is basically sub category of the initial number so an A is better than a B and in tern a B is better than a C

for example

Targeted Working At Residual

4c 4b 0.33333r

5a 4c -0.33333r

4c 5c 1

4c 5a 0.33333r

5c 3b -1.66666

so on and such forth the number and letter can be entered into seperate fields the lettering are only A,B,C but numbers can range from 1-9 i am normally quite ept with excel but this is out of my depth

On column "F" I have all the names listed, they are all lowercase, and with different length. How do I write a formula to turn those names into numbers?

For an example. In column F1 I have "James". And using letters = numbers

9 = A L T

8 = B N W

7 = E P X

6 = G R Y

5 = I J

4 = H S

3 = F Q Z

2 = D O U

1 = C M V

I would like the column G1 to display "59174"?

Would need the convention from letters to numbers on Column G adjacent with the name.

I'am trying to add a row of cell that contain a letter befor the number

I need to add-up the numbers and ignore the letter

I need to extract (and then use for SumIfs) only item numbers from the long description. Please see the attached list where item number column shows existing list & next column shows what i want to extract. The exrtacted part if has any trailing or succeeding letters, characters between numbers should stay. for example from "SGA:RV-SVA:PEPPERS/PEPPERONCINI:SV9176001/232034" I need to extract " SV9176001/232034" or from " SPICES:BULK SPICES 7100:9054B" I need to extract " 7100:9054B". Can some one please urgently help me on this.

View 9 Replies View Relateda formula that would take letter grades (A, B, C, D, and E) and produce an average letter grade?

For example, a student gets a D on one assignment and a B on another. I want a formula that will produce "C".

I need to compare for each trip whether there is a trip with Es and no Es in it and mark every line in column E with tag "Mixed Use". In the data below trip 2 has mixed use. I have attached a sample file.

Trip Employee EntertainmentMiles

Trip 1Employee 1E827

Employee 2E0

Employee 3E0

Employee 4E0

Trip 2Employee 1758

Employee 20

Employee 30

Employee 40

Employee 50

Employee 60

Employee 70

Employee 80

Employee 9E0

Employee 10E0

Need a function that would insert a letter or a number in front of numbers in a cell for example

column A

3245

I want to insert the prefix "S" in front of the nummbers 3245. so i would hopefully end up with

Column A

S3245

I use currency data pasted from legacy program that pastes dollar values followed by a C or D to represent debit and credits, ie -- 1,000,000.00C or 1,000,000.00D.

Is there some way to remove the letter and correctly format the value to a negative or positive number on a whole worksheet or column?

Currently I am doing this manually one cell at a time.

I need a formula to use on MS Excel 2007 to remove the English letters from a cell A1 and return only the numbers(if any) & Chinese characters to B1.

Please refer to the able below.

Example:

A

B

1

so l首先欢迎大家收听我们的科学与烹饪系列

首先欢迎大家收听我们的科学与烹饪系列

2

讲座的最后一次报告 有点难过这是最后一次了

讲座的最后一次报告 有点难过这是最后一次了

[Code]...

I have 2 cells, one has a first name the other has a last name for instance:

A1 = Joe

B1 = Smith

I am trying to combine the first letter of the name in A1 with the next 3 letters in B1 so that C1 = jsmi

I am trying to make a puzzle and use excel to verify I have done it correctly.

What I want to do is when I type a letter in a specific cell have it put a number value in another cell automatically. For instance, if I type the letter "a" in cell A1 I would like the number 60 to populate automatically in cell B1. I want to give each letter in the alphabet a numeric value so I can type a message in the A column and then auto sum the results of the numbers in the B column.

When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?

View 9 Replies View RelatedFor the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).

When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.

=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")

I am trying to make a formula which will tell me if A1 is a postcode (a letter and a number e.g CV42 6AQ)

In the A column, it looks like this:

CV42 6AQ

FC45 D4D

West yorkshire

PR42 6RD

Etc.

i want it to identify all the postcodes, and NOT "West Yorkshire" because it does not contain a number.

I have a column with names, where i need the 1st letters of the Names to be displayed in CAPS. If its a Single letter also, the same should be displayed in CAPS

View 2 Replies View RelatedI was wondering whether it is possible to find a formula in Excel that would extract (using RIGHT formula) surname from Name Surname cell despite surname lenght i.e.:

'John Smith' in one cell would give 'Smith' in the other

'Tom Let' in one cell would give 'Let' in the other

I have tried to combine RIGHT, FIND and PROPER (for upper case) in one formula but it does not work.

I want to put the letter A in cell a1(or any cell in column A, SHEET1) of SHEET 1 and get the information from SHEET 2, column A. Put this information in column B sheet 1. I will have about 40 columns in sheet 2. I used a if formula, but only 8 ifs can be used.

View 9 Replies View RelatedI need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.

So:

GetAwardfromBid to getAwardFromBid

or

SmallLertter to smallLetter

I'm trying to set a data validation in B1 from values in A1:A5.

A1: Car

A2: Papers

A3: Pencil

A4: Pants

A5: Shoes

What I want is show in dropdown list the words in singular (without the "s" at the end).

The formula below works if I introduce it in a normal cell,

[Code]....

or as array formula with CRTL+SHIFT+ENTER in this way

[Code] .....

But I get the message that the formula has error when I introduce any of both in "Validation Criteria"-->Allow "List"-->Source.

Is there a function that will output the column letter? For example there's one I know of: =COLUMN(), which outputs column number, but not the letter. And if not, can a formula be written to output it without converting the spreadsheet to R1C1 style or using the lookup function that refers to a separate table within the spreadsheet?

View 3 Replies View RelatedI just want to show a letter and change it to any letter and show it again in seconds. Can it be done with formula, maybe with RAND formula or any other formula.

for example when i press F9 the letter A change B, and then i press F9, it changed to C, and then when i press F9 and it changed to A again, and i press F9 and it changed to B, and so on...

(Is it possible too..with a little variation if i press F9 continually, it changed every 5 second?)

I have the following letters in cells

D2= A

D3=G

D4 =L

These represent Accomodation,Groceries, and Liquor.

Starting at E12 I want a formula , to drag down the column, which will show either Accomodation, Groceries or Liquor depending on the letter I put in cell D12,

Eventually I will possibly need help to summarise these costs on another sheet with other data such as fuel costs and fuel analysis but this will get me started.

I just have a quick question with regards to two-way interpolation of data.

I have a data table which has x-values, y-values and z-values.

I need to obtain an interpolate value for z, given a value for x and a value for y.

The x value specified is 1.569 and the y value specified is 1.66.

I know how to use the FORECAST formula for a simple x, y table, but I have no idea what to do here when I need to find a z value, given the x and y values.

I'm trying to find a way to enter the value of a cell from another worksheet into my currently active cell. The (obviously incorrect) code I'm working on looks something like this:

View 2 Replies View RelatedI would like to obtain the name of a workbook after activating it and then paste it into an input box.

How could I do this?

Well following on from my question earlier, I am still playing with my timesheet/rota I'm developing.

I have a start time and an end time, which is formatted as hh:mm then an hours cell which adds them up for you.

BUT if someone is on a rest day or a holiday day or a sick day etc, I would like them to be able to type that in without the cell that adds the hours up going doo laly!!!!! (######)

So, is there anyway they can type say 'R' and then getting the hours total cell to ignore it because it isn't a time?

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