Tracker
HOME    TRACKER    Excel

Show 1000 Number Typed As 1,000 (comma Separator)

HOw do we hard code excel to show when number typed as with the 1000 separator. For Eg: 1,000

Related Forum Messages:
Creating A Find Method To Search A Number Between 1000 - 10,000
I've been trying to put together a VBA code that allows me to find a number between 1000 - 10,000. I am able to find any number below 999 but can seem to find anything above 1000. I've been using the following ....

1,000's Of Rows To One Comma-delimted Text String
I have a worksheet with 17000 rows of words that end in commas in column A, i.e.
Column A
Row 1 = Word 1,
Row 2 = Words 2,
Row 3 - Word 3,
etc...

I need to get this into the following single string format...

Word 1,Word 2,Word 3,etc...

I want to ultimately save it as a .txt file.

Text & Number Separator
I have following data in one coloumn & in each cell it contains both txt & number. i want to separate these text & number in separate cell.

01533FHGB06533FFHHHGJJ15445W153GJGJ0656HJHJ01533DFDFGB06554FFHHHGJJ15445W153GJGJ0656HJHJFLKFSH54646FGG56464DSHJDJD54346

Multiple Cell Formatting (number Less Than 10 Multiply By 1000)
How can i format a column of cells so that if a number is less than 10 for eg that that particular cell is then multiplied by 1000.

Formula Not Working On Large Number Of Records (500,000) - 2007
I am trying to use a simple formula: COUNTIF(\$CT\$2:\$CT:\$430749,CT2)

I am trying to fill this down all the rows (430,749 rows). The formula behaves as its supposed to up to around row 650. After this point, all resulting values are all the same, which happens to be the same value that was returned from the original formula in C2. However, this is not correct. It's like the formula just stops working after row ~650. I tried F9 without success.

Number Format: Change Decimal To Comma
Is there any way to change the decimal point from the usual period symbol (.) to a comma (,). The reason being, i have a structural design spreadsheet and the new terminology is just so, changed from 00.00 to 00,00 I have tried cell formatting but it just uses a comma (,) for numbers in their thousand and it wont do what i need.

Number Add To Comma Separated Data In Cell
Cell(i,1)have 3 Numbers

Each Number Not Allowed Greater Than 10

Each Number In Cell(i,1) Will Be Added 1 In Cell(i,3) And Cell(i+1,3)....

How Can I Seperate Numbers And Make Three Variables To Run Macro
A
1,3,10
2,5,9
C
2,3,10
1,4,10
3,5,9
2,6,9
2,5,10

Find Number In A Cell With More Than 2 Numbers Separated By Comma
When the entry in a cell is of the format e.g 3,4 is there any way to find the value 3 or 4 in that cell?

Attempts like:

Cells(i, 1).Value = variable (e.g variable=3)

or

Set cell_found = Cells.Range("A1:A10").Find(variable, lookat:=xlWhole)

do not find the cell with the 3 (or 4). The above work fine when 3 or 4 are alone in the cell. Is there anyway to find these values in the cell?

Clear Cell If Number Matches Comma Seperated Numbers
I found code on this site after searching a long time that has worked well in a larger macro I'm writing for use in Excel 2003 usage. However, I'm stumped on how to achieve the following on the attached sample: find and remove the number in A2 from the list in E2 then count the individual numbers seperated by commas in E2 and post the results in G2 Repeat the same sequence for C2, D2 and F2

Thousands Separator
On my indows XP box, when I type a number into an Excel spreadsheet and it is in "General" format, the numbers appears like this (examples):

1234567

or this:

7.125

or this:

8.1

I want them to look just like that except for the first one, which I want to have the thousands separator:

1,234,567

I don't want to use a number format because
Excel makes you set a specific number of decimal places...I want the decimal places to float depending on how many decimal places there are in the entered number (just like the "General" format does). So basically, I want a "General" format, but with thousands separators (commas).

I tried using something like ###,###.### but that leaves a decimal place at the end of each integer (i.e. "123,456.") which is also not acceptable.

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

Text-to-column, Any Figure As Separator.
I have a column with a number of strings looking like below examples

I am looking to semi automate and mass emailing routine. I done code to validate the email now I just want to do some code to append the validated emails. Was hoping someone could offer a quick and easy way to accomplish this task. Below are a list of emails as they would be in excel below that is a variable “strRecipients” that is used to append all emails.

jey.boks@some.state.tx.us
Breay@iglobal.net
rccep@chiter.net
edd.champ@moeaent.com

strRecipients = strRecipients & ";" & ""jey.boks@some.state.tx.us""
strRecipients = strRecipients & ";" & "Breay@iglobal.net"
strRecipients = strRecipients & ";" & "rccep@chiter.net"
strRecipients = strRecipients & ";" & "edd.champ@moeaent.com"

Lakh Separator. Indian Rupee Cell Format
to format cells with "lakh" separator.

Excel allows only thousand's separator.

The other option is to make the changes in the regional settings... which will
affect all other application.

Replace Dots In Dates With Valid Date Separator
I have a SAP application that outputs a field of data from a specific query. One column, the date, is produced in the format 18.02.2009. Is there any excel 'trickery' that can convert this into a standard british date format i.e. 18/02/2009 & then allow me to sort the entire data field by ascending date order?

If A1 = 500 Than 10, If It =1000 Than 20 Etc.
A1 would be the cell that users would enter quantities of 500, 1000, 1500, 2000 etc and based upon what they entered-cell a2 would give a corresponding entry of 10,20, 30 etc.

Formula For A Letter Typed
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.

The Formula You Typed Contains An Error ..
I am getting the 'the formula you typed contains an error" ....

VLOOKUP And Still Have Typed Text In Cell
I was wondering if there would be a way to use vlookup and still be able to type text in the same cell. For example, if the vlookup gives me a result of 123. I want it to also pull from a different cell that I've typed in 321. So the resulting cell would show 321-123 or 321 123. I have attached a sample sheet. On sheet two where the 3 items are, I want it to display 321-123 for item 1, 321-234 for item 2, and 321-345 for item 3. The 321 would be dependant on what I type into B1 on the first sheet.

Convert Variant To Typed Array
I am using strongly typed code, but I am also pulling data from worksheets into variants. e.g.

Dim MyArray As Variant

MyArray = [MyNamedRange]

is there any shortcut to convert the variant to a typed array, which doesn't just involve looping throught the variant and using a casting function, e.g.

Dim MyArray As Variant
Dim typedArray() As type

MyArray = [MyNamedRange]
typedArray = shortcut(MyArray)

Vlookup: Divide It By 1000 Then Multipy By 24
I am having a problem with vlookup. I have the formula working but would like to divide it by 1000 then multipy by 24. When I enter /1000*24 after the colon in the vlookup formula my answer is 0(zero). If I don't try to divide and multiply the answer is correct.

Formula- Figure Of 1000 In Cell A1
i have a figure of 1000 in cell a1. i want to divide it by a figure in cell b1 and have an answer in c1, or divide it by a figure in c1 and have an answer in b1.

in summary, cell a1 has i figure in it while cells b1 and c1 are empty until i enter a number into one or the other.

Condensing 1000's Of Columns Into Just 3
Based on the 1st example below, I have a file with 1000's of columns where every 3 columns has the same type of info....

I made a thread similar to this earlier but was unable to get to a desired end result.

I am trying to make a formula that will count the number of cells that do NOT have a fill color AND have a value over 1000 AND are greater than 60 days old as of the last day of the previous month.

Attached is a file with sample data so you can better understand

I included 2 formulas in the file that I am already using. Just in case that information is needed/relevant.

Change Formatting On One Sheet When Text Is Typed In Another
I am trying to have one sheet with company names on it and another as a schedule. I have about 20 companies that I need to list on the main schedule sheet. Is there anyway when I type a company name in one sheet, when it copies that cell to the schedule sheet, format that cell to a specified format? I know conditional formatting will work, but it only allows 3. I need around 20. I would just like to change the background color of that cell when the name is typed in.

Auto Select ListBox By Letter Typed
From VBA code - I need to be able to populate a list box with a assortment of city names, (>200 entries), and be able to start the city selection with an alphanumeric “hot” key in the pick box – i.e. – when the user enters an “S” – the list box data jumps down to the start of all of the cities beginning with “S” – etc.
(I can populate the list box) – it is the alpha selection code that is kicking my butt.

Looping Through And Processing 1000 Records At A Time.
about making my code a bit more useful.

I started with a basic code as below which basically looks at upto 1000 part ids in column A and goes to my database to find corresponding Product Codes from a table in the database call PART

What Numbers Have No Decimals In About 500-1000 Cells
I use a worksheet full of formulas to know what divisors a number have, but i need to see what numbers have no decimals in about 500-1000 cells. Is there a formula whitch computer can use to see if it shows a number or not (if a number have or dont have decimals)?

Worksheet Change Event To Pop Up A Message When Typed
I have the following code that should pop up a message when column E is left blank when "other" is typed into column D. But I cannot get it to work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, LastRow As Range
Dim x As Integer
If Target.Count > 1 Then Exit Sub
Set rng = Range("F:F")
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target = "" Then
For x = 1 To 5000
If Cells(x, 4).Value = "Other" And Cells(x, 5).Value = "" Then
End If
Next x
End If
End Sub

Auto-replace Typed Contents In A Cell With A Formula
I want to set up a sheet macro that will replace the contents of a cell (when a specific number is typed in) with a specific formula.

For instance, when "43" is typed into any cell within specified columns (say columns C and H), it changes to the following formula: =IF(D47="",43,IF(D47<=3030,408,43))(WHERE D47 WOULD REPRESENT THE CELL ADJACENT TO THE CELL WHERE "43" WAS TYPED.)

IE:

If "43" was typed into cell C1, then it would change to the formula: =IF(D1="",43,IF(D1<=3030,408,43))

If "43" was typed into cell C50, then it would change to the formula: =IF(D50="",43,IF(D50<=3030,408,43))

500,000 Dependencies
My report ran great on small data set, but on large data set the calculation has scrolled to a crawl. (A one hour crawl in 2003 and a 6 minute crawl in 2007.) I read something about dependency limits, and ran the macro provided on Mr.Excel, which calculated close to 500,000 dependencies, which I assume is my problem. Then again, I thought I couldn't have more than 65,000 or so dependencies, and if that's the case why does it run at all?

Sheet1 has about 25 columns, most with sumproduct formulas that check for data returned by queries on 11 or so other sheets. For example:

=SUMPRODUCT(('Job Costs'!M\$2:M\$14180=C728)*('Job Costs'!F\$2:F\$14180>=\$U\$6)*('Job Costs'!F\$2:F\$14180

Consolidate Over 60,000 Rows
I have three worksheets with a large amount of data on them (~60,000 lines). The column titles and layout are exactly the same. I need to either 1) Get all of the data into one worksheet so I can create a standard pivot table, or 2) Analyze all of the information using a multiple consolidation range pivot. When I try to copy the data from one spreadsheet to another I get an error message stating the copy area is a different size or shape from the paste area, even though it is not. When I try to create a pivot table using multiple consolidation ranges the fields returned are Column, Row, etc instead of the data fields I would expect (the column titles - exactly the same in each worksheet).

How Many Times A Number Show Up
I want a cell to tell me how many times the number 12 is in a group of cells. Is there a formula or something that will.

Export Data As Text 1000 Rows Per File
I'm trying to accomplish is to take an Excel file that contains one column of data consisting of up the maximum number of rows of data (numbers formatted as text? "000000000") and export the data to a text (.txt) file 1000 rows at a time. I would also like the code to allow me to name the .txt files in succession, for example, Pg01, Pg02, Pg03, etc.

The files are going to be used to query a system that will only accept text input 1000 items at a time.

I run the following code to ensure the data is formatted consistently:

Sub a_VerifyDataForInput()
' Start at Cell A1
Range("A1").Select
' Select Column A
Columns("A:A").Select
' Format data in Column A
Selection.NumberFormat = "000000000"
' Replace all "|" (whatever you call this thing ... pipe???)
Selection.Replace What:="|", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' Replace all "-" (dashes)
Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _ ........................

Transform 1000 Rows X 50 Columns Table Into 2columns
Am working with a spreadsheet of about 1000 rows and 50 columns which I need to transform into 2 columns and as many rows as needed. I have attached a simplified example of a 3x3 spreadsheet so you can see what I mean. I have already managed to get my result using Filtering and some copy and paste but I am struggling to now fully automate the solution.

Forumla For The Calculating Total TIME But To Give A Value As 0 When Text Is Typed
I have set up a spreadsheet to calculate time as followed:

A1 - Start Time
B1 - Finish Time
A2 - Hours Worked:

=IF(B1<A1,B1+1,B1)-A1

C1 - Start Time
B1 - Finish Time
C2 - Hours Worked:

=IF(D1<C1,D1+1,D1)-C1

and so on.........

I’d need to store the questions, passwords/ answers somewhere for VB to look up I guess??

Capital of England – London
Capital of France – Paris
Capital of Italy – Rome

Etc etc

And then I need coding which will open one of the random questions, which will only grant access to the spreadsheet if the correct answer is typed.

How Do I Macro 20,000+ Series Of Data?
The full s/s stretches to nearly 350,000 rows and consists of over 20,000 series of data, between 6 and 30 rows deep, in columns A:J.

In column I of each series, on rows 3, 4 and 5 there are three numbers.

The second number is to be subtracted from the first with the answer being divided by the third.
If possible, the answers should appear in the rows directly beneath these three numbers.

Because there are so many series of data it isn't feasible to manually input the formula and I'm searching for a macro(s) which can do it automatically.

Can anyone please suggest code to help me to this end?

Pasted Time Changing 00:00:00:000 To 00:00.0
I am trying to enter 00:00:00.031, but it is changing it to 00:00.0. Can someone please tell me how to stop it from doing that?

Converting 10.27M To 10,270,000...
The issue is that there are six types of "numbers" in the table that I am trying to turn into all numbers. Obviously, I'd like to turn the Ks in 1,000s and the Ms into 1,000,000s:

#.##K
#.#K
#K
#.##M
#.#M
#M

If there weren't all six, I think I could fix this pretty easily, but as it is I'm pretty stumped (other than a manual fix).

If Cell Has Number Then Equal, Else Show Nothing
If square A1 says deposit account and c1 has a figure in it, i want g1 to repeat the figure,but if a2,a3 etc says something other than deposit account i want g2,g3 to remain blank.What is the formula for square g1etc?

Make The Auto-filter Range Bigger Than 1000 Cells
Is there any way witch i can use to make the auto-filter range bigger than 1000 cells?

Check If Text Typed In Cell Is Repeated In Row, Discounting Spaces
is there a formula which can locate instances of same text even though due to spaces it may look different? for example, "bad apple" and "badapple" are essentially the same.

Macro To Sort 100 000 Rows
I've got up to 150000 rows in excel (2007) and i need to sort the data into 2 sheets.
Sheet1 contains all the data. Sample:

1000999999027FRESH PASTA FETTUCINE WITH TOM102002P N0809100000035000000012810000000+0000000+ 0000000000000000+0203001896409
2000999999027020300189640900000000000005740200070063

As you can see, the first row starts with 1000 and second with 2000. That would be the criteria. All other rows goes exactly the same.

I need macro that copies all the rows starting with 1000 onto sheet2 and all the rows starting with 2000 onto sheet3.

Filtering and copy/paste won't help, because the list is toooooo big. Thatswhy only hope is to find macro that loops threw the list.

Transposing 25,000 Rows Of Data Into Columns
I really could use some help here. I have ~25,000 rows of data, contact information, and I am trying to convert it to columns with the corresponding 9 column headers (Full Name, Job Title, Company, Business Street, Busines City, State, Business Phone, Business Fax, Web Page, E-Mail Address). The challenge is that each record does NOT have the same number of rows...?? I am new to this forum, so please forgive me if this a stupid question. I've attached the data for three records below and also in the attached spreadsheet ...

Hyperlink 120,000 Cells In One Sheet
I have a excel worksheet (2000 format generated by java excel api)that needs to hyperlink roughly 120,000 cells. After hyperlinking exactly 65534 cells, the remaining cells are just underlined blue text w/o hyperlinks. Even when it's saved as Excel 2007 worksheet, I was unable to add anymore hyperlinks manually.

I don't know if there is a way to make it work?

There are 30,970 rows and 11 columns in the sheet.

Is there any solution that does not involve spliting sheet, greatly increasing # of columns, or greatly increasing # of rows.

Beating The 32,000 Datapoint Chart Limit
I'm trying to come up with a little code that will select 32000 line sections of a 500,000 line dataset, and add each one as a series to an XY scatter chart. Unfortunately, I the macro recorder doesn't show any actions involving charts this a setting somewhere). What is the VBA command / syntax for adding / formatting series?

Left & Right Formula To Show Number Not/as Well
I have numbers in a cell such as 1/25, 4/31, 12/35 etc I have the current formula to extract the left or right number but when there is only a single digit it also includes the / is there a way of eliminating this.

The numbers vary from 1 to 2 digits.

My current formula is =IF(C15<18,LEFT(E8,2))+IF(C15>18,RIGHT(E8,2))

I only want it to show the number not the forward slash

Show Only The Number Of A Cell Containing Text And Numbers
Lets say cells A1 to A5 contain these lines of information:

Till 174 (T0215) - till keeps turning itself off.
Till 245 - stuck on windows screen
116 - keyboard is unresponsive
Berkel Scale is constantly beeping
ped not reading cards Till 156

How can I show only the numbers from these cells (i.e. B1 will be 174, B2 will be 245 etc...)? As you can see the number isn't always in the same place, and doesnt always have the same characters either side.

Format Number Of Decimal Places To Show
Is there a way to format a cell based upon a condition? If the cell value is <1, I want to show two decimal places. If the cell is >1, I want to show zero decimal places. I tried to use the conditional formatting, but there is no option for this.