Tracker
HOME    TRACKER    Excel

# Format A Number As 1st, 2nd, 3rd Etc.

## I cannot figure this out, but does anyone know the custom format code to do a number into 1st, 2nd, 3rd, 4th, 5th, etc.

Related Forum Messages:
Find 1st, 2nd And 3rd Place...?
We're doing a "Biggest Loser" competition at work. I have a spreadsheet that calculates the % of body weight lost. I need a formula that will look at the results and display 1st, 2nd, 3rd places. Here is this weeks results...

Take The First Letter From The 1st, 2nd, 3rd Names Of The List
I have a few names in the range A2:A11 and the exercise asks: Fill in the cell B19 with a temporary software name. Using the appropriate function, compose such name as follow: take the first letter from the 1st, 2nd, 3rd names of the list; take the third letter from the 4th, 5th, 6th names of the list; take the last letter from the 7th, 8th, 9th, 10th names of the list. Moreover, the software name must be in capital letters.

Locate & Return The 1st Or 2nd Or 3rd Value
I have one column of data (numbers). Within this column, are the values for two different categories...which are separated by a blank row. And then a total for each product given one column over; like this:

Product1 123
Product1 456
Product1 789 1368

Product2 456
Product2 123
Product2 456 1035

Either product can increase/decrease in rows, so I am unable to set a static range. The only solution I got on my own was to use SUMIF (sum the column of values if it matches the name "Product1" or "Product2"). This is fine until someone misspells the product name or leaves it off completely; which is why I prefer to go off the actual values.

My 1st preference is to go off the totals. Basically, return the 1st value (ie 1368) in the column, and then in another cell, return the 2nd value in the column (ie 1035).

My 2nd preference would be to sum the individual values in the column until a blank cell is encountered, and then return that value. Then continue on until another blank cell is encountered, and return that value as well.

I know very little of vba, so I haven't taken that route, but I bet this is easily done as a UDF (although I would prefer not to do it that way).

Conditional Format For 2nd And 3rd Highest Value
I am trying to use conditional formatting to highlight the 2nd and 3rd highest value in a given column.

Example:
Step John Sam Hugh
1 3 4 5
2 9 10 23
3 12 11 43

Based on the above, for step 1, Sam has the 2nd highest value and John has the 3rd highest value.

Is there a way to use conditional formatting to highlight this ?

Range Object/property: Calculates 2nd Column Based On 3rd Column's Number
I'm trying to write a code that calculates 2nd column based on 3rd column's number or vice versa based on the condition set on the 1st column. Below, there are two procedures. "SimpleCalc" and "SimpleCalc2". I first wrote SimpleCalc2, but it isn't working, so I worked around the error by writing SimpleCalc, which selects a cell and moves around by offset. I personally find it hard to read and error prone as I develop more logic around it.

I'm trying to develop more function based on this code, so I need to make it neat and flexible. what I'm doing wrong in SimpleCalc2? Or do you have any suggestion to improve the code "SimpleCalc"? I'm using Excel 2003.

Sub SimpleCalc()

Dim SimpleMethodRng, SimpleMethod As String, i As Integer

ActiveWorkbook.Worksheets("Dashboard").Range("P5").Select

SimpleMethodRng = ActiveWorkbook.Worksheets("Dashboard").Range("N5:P12")

For i = 1 To 8

SimpleMethod = SimpleMethodRng(i, 1).........

One Sheet Saves A Column To The 1st Empty Row On A 2nd Sheet After 1st Looking For A Match On An ID Variable
I am trying to create a macro so that when i save an entry the form sheet looks in the tavela master sheet to see if there is a match on the record number (in the A column) and saves there as an update if it matches and then if there is no match, saves it in the last empty row. this is a column to row copy so it has to be paste special to transpose. also i have a password on the sheet which i use to unprotect then reprotect. i have successfully done these two things separately but cannot combine them! i know not very much about visual basic code just what i have read here and so i am trying to make this code work.

Code:....

Return 2nd And 3rd Instances
I have text strings containing 3 commas at all times (see below for example). All instances of the commas are names at either end of the comma (Last, First). My request is how would I be able to retrieve *only* the 2nd and 3rd names (in separate cells) and omitting the rest of the text?

Example:
A1 - Source
2/1/2010 Random - Text - Containing 1LastName, 1FirstName 2LastName, 2FirstName 3LastName, 3FirstName Some Other Text 555555556 1234567890 1 0 0

B1 - Requested
2LastName, 2FirstName

C1 - Requested
3LastName, 3FirstName

Adding A 2nd And 3rd Series Onto A Chart
I have the following code which puts a series onto a chart

With Charts("Test Chart")
.Axes(xlCategory).MinimumScale = 1
.Axes(xlCategory).MaximumScale = Nteeth
.Axes(xlValue).MinimumScale = Miny
.Axes(xlValue).MaximumScale = Maxy
End With

I would like to add a 2nd or third series onto the same chart using the same scale etc. How do I do this in VBA code? Whenever I add another line to add a series it just overwrites the first series I have shown here.

Parse Data Between 2nd And 3rd Delimiter
I'm looking for a worksheet function that will allow me to separate into another cell the characters between the second and third slash in the source cell. The number of characters varies, but the value I want is always between the second and third slash.

2nd , 3rd And 4th Date Find From List Of Dates
how to find the 2nd, 3rd and 4th date from list of dates.....

Margin Between 1st And 2nd
I have events in column1 and ratings in column 2. The ratings are not sorted.I column 3 I want print the difference between the highest rating in event 1 and the second highest e.g. in the attached sheet column 3,rows 2 to 7 will contain 13.9. Similiarly for event 2 etc down my full sheet of 1200 events.

Vlookup: 1 Value On 1st Sheet V Up To 3 On 2nd
I want to do a vlookup between two sheets, the matching criteria on one sheet is in the format xxxxxxxx ABC and each will only appear once. On the second sheet the same match may appear between 1 and 3 times, these will be sorted so will be beside each other. Is it possible to return the sum of the qtys for each time the criteria appears on sheet2 using a formula rather than the first one as a conventional vlookup would?

Sheet 1
Criteria Qty
xxxxxxxx ABC 100
Sheet 2
xxxxxxxx ABC 50
xxxxxxxx ABC 30
xxxxxxxx ABC 20

Result
Criteria Sheet1 Qty Sheet2 Qty
xxxxxxxx ABC 100 100

Calculate 1st Tuesday Or 3rd Friday Of Month
I am trying to construct a schedule of tasks to be done by certain dates. If the last day of the month is entered (say 11/31/09) in a cell, is it possible to find out the date for the third Thursday of November or the first Wednesday or the last Friday?

2nd Mode - 2nd Most Frequent Number
In this old thread (which the software did not let me continue) Yogi mentions 2nd mode and third mode etc.

[url]

In the above thread, Yogi has a nice formula for finding the "other mode", when the other mode is a tie. I am wondering - is there a simple formula
to find the 2nd (or 3rd) most frequent number. For example the (AFAIK non-existent) formula or function would act like this:

1
1
1
3
3
2

"modelarge(range,2)" should return 3

If 1st Wednes Day Or 3rd Of The Month Then Hightlight The Celll
looking for a formula that will tell me if a cell containing a date
is the 1st wed of the month or a 3rd wed of the month then
hightlight the celll

example
=if(A1=1st wed or 3rd wed,A2=green,"")

Extract 1st And 2nd Character Into Seperate Cells
I am attempting to make a macro for. It requires me to scan a column of cells worth of data (characters) for parts of what they contain and place those parts into a variable or string so that I can use an IF statement to place text in another cell.

I used to know someone who could do this via VBA, but I have been unable to reach him.

This sheet is fairly basic. The cells to be scanned are all in one column, they are all four digit numbers and I need to read the first two digits into separate variables.

Example: If the cell has "4101" in it, I want to be able to read the first digit "4" into one variable and the second digit "1" into another variable.

This way I can place text based on either one of those digits into the next cell over with an IF statement and can enclose it into a large loop to do the entire column.

MID Formula: Extract Only The Text Between The 1st And 2nd Dash In A Cell
I need a formula that will extract only the text between the 1st and 2nd dash in a cell. Example:

10466193-1909457-
10648145-3026718-t526363536m

This formula would return only the following:

1909457
3026718

Paste 1st & 2nd Columns With Dependant List Boxes
I would to say thank you to RoyUK for helping me out with this DependantlistBoxes where a Userform has two columns. Now I've manupilated a Userform so that whenever I double click on the cell, the selected text from the second box in Userform is pasted to the cell in the next column to the right.

But I what I really want it to do is to past the text fromt the first box in Userform to the active cell and when move to the second box in Userform, the selected text will be paste to the cell on the right.

Like I double click on B3 (in "Fullarton") sheet, a userform appear. When I choose CB from the first box in userform, it should paste in B3. Then when I choose Electrician from second box in userform, Electrician should be paste in C3.

If 1st Condition Met Don't Execute 2nd IF Statement
I'm having a problem with negative values. Somehow if the pitchX is -11.1877, let's say, and it is the right value, the code will still perform the 2nd if statement, when it should not. Am I using the wrong data type? Or is there anything I'm missing out?

Dim pitchX As Double
If pitchX > 10000 Then
pitchX = pitchX / 1000
End If

If pitchX <> Range("C8").Value Then
With Range("C8")
.Font.Bold = True
End With
End If

Return Value From 2nd Column When Value In 1st Column Matches Given Criteria
I have a column containing text values eg M1, T2, M3, A4 etc. and I am trying to return the value (numeric) in a second column to a cell when the text value in the first column is matched.

Populate 2nd Listbox With 1st Listbox Selection
I have the following sheet which functions as a table to store values for files that have been created using the application which this table is in. In this app., I have a form with 2 listboxes. When the form loads, I have the first listbox list values which each of these files are listed under (i.e. - "sub-directories"). With a selection of one of the list values and clicking of a button, I want the second list box to list the values of cells listed in a range directly below where the selected value in the first listbox came from.

I'd prefer, in the first listbox, to have only the values of the ranges that have a value in them in the listbox. However, this would cause my listbox.selected(array) not function properly. But since my current offsets (in the second sub) do not seem to be working anyway, maybe I am going about this totally wrong.

Append 2nd Row To 1st Row
I've got data that should be on own row but its come into my excel file as two rows. This is going to be done daily, as a report so its not just once off, i need a macro or something.

so every second row should be appended to the right of each first row
e.g.

Row 1 First Name
Row 2 Last Name
Row 3 First Name
Row 4 Last Name
Row 5 First Name
Row 6 Last Name

I would like:

Row 1 First Name Last Name
Row 2 First Name Last Name
Row 3 First Name Last Name

Read The Number And Check For The First Number With The 2nd Number
I have this set of number..

987654.

What im trying to do is this..I read this number and check for the first number with the 2nd number. If its greater then i will swap it. Im trying to do this so that my number can be rearranged as 456789.

Matching 2 Fields In 2 Columns And Copying A 3rd Field Into The 3rd Column
I need a formula. Probably Vlookup. I'm having a LOT of trouble trying to figure out how to "phrase" the formula so let me explain what it is I'm trying to do. If a cell in Sheet 1 Column AS and a cell in Sheet 2 Column B match exactly, I want the cell in Sheet 2 Column C that is in the same row as the matched cell in Sheet 2 Column B to be copied into Sheet 1 Column BB in the same row as the matched cell in Sheet 1 Column AS.

For example, if cells in S1 Column AS and S2 Column B both have the name "LOS ANGELES" then "KLAX" will be copied from cell in S2 Column C (in same row as "LOS ANGELES") to S1 Column BB (in same row as "LOS ANGELES").

Getting 2nd To Last Number In A Range
I know that MIN(A1:A5) will give me the smallest number from A1 to A5, but is there a way to get the 2nd lowest number in a range?

Highlight 2nd Highest Number
I'm using this CF to show me the largest number in a range;

=F10=MAX(F\$10:F\$33)

I now want to also show the second largest number in the same range.

Date Range (formula Show A Week Number In The 2nd Table )
I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates.

Example:
1st Table
FROM TO WK
3/27/2009 4/2/200914
4/3/2009 4/9/200915
4/10/2009 4/16/200916
4/17/2009 4/23/200917
4/24/2009 4/30/200918

2nd Table
DATE
03/28/2009
04/11/2009
04/26/2009

Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.

Move 1st X Digits Of Number To End
I need a formula to extract the first two numbers and move them to the back of the number remaining. For example, the original number is 235871, the result would be 587123.

My numbers always have six digits but may at times have seven.

Row Number Of 1st Empty Cell In Column
MATCH(3, A:A, 0) will return the row number with the first number '3' in column A. I need to search for the first empty cell in column A.

Save Number As Text In Txt Format Wo Changing The Format
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.

First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.

Format Number To Text With Specific Format
I'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:

474556788 to 047-455678-8

Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)

Show Text & Number With Number Format
I have a standard block of text with numbers in it pulled from various calculations in a financial model. I have done this through a formula

e.g. ="You gross profit percentage is " & D9 & "% and your gross profit is \$" & D10 & "." Problem is i'd like to format the numbers that pull through so they are easier to read. At the moment in the above example D10 results in \$-600000000. I'd like it to look like \$(600,000,000).

Format Cells So Number Shows As Number Over Zero
how do you place 100/0 so the /0 stays static and the 100 can be used to operate? (ie: 100/00, 101/00, etc)

Conditional Number Format - To Be Used As Number
In Excel 2003, I have a series of complex(ish) lookups which populate cells in Sheet 1 from other sheets in the same workbook. Some of these cells are %ges some are not, I need to display the resultant sheet values as %ges or not based on if the cell to the left (also populated by a complex(ish) lookup) contains the character "%".

I cannot just format the cell on its own, as the list in itself is volatile and can change according to the complex(ish) lookup,

The main problem with this is I need the result to be a numeric value and NOT text in ALL cases as further calculations will be undertaken on them.

In simple if it is a %ge I need to see the %ge and use it as a %ge or if it is just a number see it as a number and use it as a number.

e.g

A B Result Req
Satisfaction with telephone access (%) 0.900032390.0%
Ability to see boss within 48 hours () 0.889 0.889

Join Text & Number & Retain Number Format
I used to get data from a database (CorVu & MIMS) in this format "0122458/001". Due to changes in those Databases I now get the data as 2 columns " 0122458" and "1" .What I need to do is somehow get this back to the old format including the leading zeros.

Data Validation Format Letter Number Letter Number Etc.
I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.

Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.

Split 1st & Last Names & Split Addresses After 1st Comma
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".
####################################################

Macro Changes Number Format
From an accounting system I download txt files that then are read into Excel in order to be workable. The numbers come out like this 1.234,00 (ie decimal as comma separator and dot as thousand separator). This does not work in Sweden so I need to get rid of the dot.
I have built a macro that apart from taking out the dot makes some other changes to the data so that one wont have to do the same thing everytime ones downloads the same type of file. The problem is that when creating the macro or just doing the text to column, number format, replacing . with nothing it all works well but when I run the macro some of the numbers become text, others are ok and for some of them the macro does not only take out the dot but also the comma (which makes a huge difference). I have both Excel 2003 and 2007 and the same thing happens with both.....

Changing Number Format
Please take a look at the attached spreadsheet. in the ODDS column D, after download the some of the data has been changed from say 100-1 to a series of numbers how do i change them back i would also like to show the odds is they were 100-1 as just 100 and say if the odds were 13/2 then i want to show 7.5 basically i want th first number divided by the second.

Number And Date Format
In Excel is there are way to format a cell automatically dependent on the value in another cell? Specifically, if the user selects "410" from a list in cell O9 and enters numeric characters into cell K6 the format of cell K6 is a whole number. However, if the user selects any other number from the list in cell O9 and enters numeric characters into cell K6 the format of cell K6 is now in dollars. I tried using custom in Excel, which I really would like have it in, with no sucess. I am now thinking the only way to accomplish this task is by VBA code. I am ok with formulas; VBA is a different story.

Number Format For Inventory
I'm using excel as an inventory database but need to know how to change the number format according to what the user is keeping track of. For example drums, bags , cylinders ect...

How can i change the number format to display the unit of measurement added by user?

Custom Number Format ...
This workbook has 23 sheets for which this format will apply.

Under 1 man-hour .###
Over 1 man-hour #.##
Over 10 man-hour ##.#
Over 100 man-hour ###
Over 1000 man-hour #,###

Format Into Text/ Number Value
I have a data set which is in a date format of MM/DD/YY. I have converted it to YYYYMMDD. I now need to copy and permanently past the values so that the value within the cell always reads to result of the date in the format of YYYYMMDD.

Example:
-current = 07/01/08
-new format = 20080701 (cell still reads 07/01/08)
-would like the cell to read 20080701

My main goal is to bring this into Access and use this dataset with another data set, which uses the YYYYMMDD format.

Number Format Switch
I have several phone numbers that I copy and paste from the web:
I.E:

(888) 456-7890
(333) 234-5678

I need to switch all of them to just standard numbers: They should look like this:

8884567890
3332345678

I go to "format" - "cells" - "numbers" but nothing works. I wonder if any formula can do it. It should work for ALL THE LIST, I want something else than change them one by one by hand.

Custom Number Format..?
If I have the following info:

A1 = 5
A2 = -4
A3 = 0

How can I use Format Cells > Custom to have them display as:

A1 = 5 up
A2 = 4 dn
A3 = Level

I've been trying and trying but keep getting a message that I should use one of Excel's pre-defined formats.

IF Function For A Number Format
I need a formulat that will ensure that a cell has 12 numeric characters,a dash, and then 3 more numeric characters. I thought this was a simple formula but I cannot get anything to work.

############-###

Number Format In Comment
I am tring to writh the value of the cell formated as "#,##0.000" inside the comment box in this format,...............but ...........could not make it

here is my try

Sub Test()
Dim cf As Double, c As Range

For Each c In Selection
If c <> 0 And IsNumeric(c) Then
cf = Format(c, "#,##0.000")
With c

Textbox Number Format
what the format would be for 3 numbers in a userform textbox. No decimals, no symbals, just the 3 numbers.

Format: Number And Reverse
I have a userform into which a user can enter a number. When they exit the TextBox the number that they entered must be formatted into the number format. eg 12345,67 -> 12.345,67 and -12345,67 -> (12.345,67). To do this is not the problem: formatnumber does the trick. What I am struggling with is to get VB to still recognise this as a number. I need the code that does the opposite of formatnumber. (12.345,67) -> -12345,67. I excell it works perfectly when you format a cell as custom: _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_). I want to be able to do the exact same thing in VB.

Indian Number Format
for the number inserted in A1 if I pressed comma button it give me 1,000,000 as in the regional setting group of 3 in given. My question is when I press comma button for the inserted value i.e. 1000000 it should give me 10,00,000. First group is of 3 digit and after that all gourps are of 2 digit

Value Excel Gives Result I required this kind of formatting

1000000 1,000,000 10,00,000

2023154568 2,023,154,568 2,02,31,54,568