In our language the thousand groups between numbers are separated with a space, like 2 345 345. (so we do not use , or . ) How can I do it in a macro, is there any possibility?
How should I change this: MERGEFIELD brutto # ### # ## ### Ft.
(I know this is without braces, I cant copy them). so this gives me a result 2345678 Ft. and I want 2 345 678 Ft.
I have data that I converted from txt. The problem is, upon conversion it has spaces at the end. For example:
"Monkey "
"Elephant "
"Birds "
What I need to do to remove the spaces?
I need to convert thousands of files from .csv to .xls format. Filenames are numbers (1.csv ... 20000.csv). Filenames do not change.
Could just open, Save As, and Close.
Is there a simple way to run a macro once and convert everything? I don't know how to code the macro to open one file, convert, then close, then open the next...
I've had a macro running for ages, and just found it bugs out when there are spaces in a sheet name.
The user selects a destination cell as part of a form, which passes the variable to the following steps:
Code:
looperx = 0
Do
looperx = looperx + 1
Loop Until Mid(celldestinationstring, looperx, 1) = "!"
Sheets(Left(celldestinationstring, looperx - 1)).Select
The purpose of which is to isolate the sheet name from the cell reference, which is typically like: 'Bob Sheet'!$B$9
This works fine where there are no spaces, but 'dies' with a runtime error when there are. With the apostrophes I assumed it would work around spaces in the string but I can't get it to work.
I have a excel spread sheet, which consists of huge numerical data. I have Huge data in Column “C1 to C350” and each cell Contains numbers like “ 456 584” and “5899 528”. So, I need a macro to give enter instead of space between the numbers.(i.e. 456(here enter should give)854).
View 9 Replies View RelatedIs there a macro I can write that will delete all spaces in all the sheet names in a workbook?
View 4 Replies View Relatedin creating a macro that will remove all frontspaces, trailing spaces from entire sheet + remove characters like (), *,-, &, @,/,',;,. from columns E and F,
in col E and F there should either be string or numbers, everything else should be removed.
I have created (via recording) a very simple macro in Microsoft Word to convert a fraction of the form a/b to the form where a is directly over b with a horizontal line between them (like how you would actually write it in math class). Before I send it to my co-workers, I want to beef it up a little.
Right now, it will only work if a and b are integers or if a and b are words or phrases with no spaces (i.e. change/time will convert correctly but the phrase change in x/change in y will not). Also, it would be great if it could work with parenthesis (i.e. (2x-1)/(2x+1)).
Just so you know, I am very comfortable working with code in order to come up with a beefier macro. However, Word uses Visual Basic and I'm afraid I don't know much about the syntax of that code. So, if any of you know of a site I could view to learn a little Visual Basic or if you know a site that already has good code already written for this purpose, I would be much obliged.
I'm working on a sheet with about 10,000 rows and 8 columns worth of data. Most of them are formatted as 12345,12345,12345,23456 how they are supposed to be, but some of them will have spaces inbetween each sequence, or some even a couple spaces, or spaces at the end. For the most part I can use the replacement function with ", " to "," but some of the double spaces throw it off, and end spaces also. Is there a macro that can just search through the selected cells I pick to just remove all spaces so the data falls back onto the commas?
Working in excel 2010
One of my colleagues wants Excel only to show whole thousands, but I am not sure how to do this
For example, if the number is 78654.98, he only wants it to show 78.
Is this possible?
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.
I am using Excel 2007. I have pasted my code below. It works fine unless it has a space, in which case it inserts %20. In this case, sRange2 = ActiveSheet.Range("E11") will always have a space in it since this is where i store a job's name.
Sub Macro1()
Dim sRange1 As String
Dim sRange2 As String
Dim sRange3 As String
Dim sRange4 As String
Dim sFullPath As String
[code]....
I am writing some code for a fast vlookup. I have the following template. The formulas in column AU and AX need to be automated. actual file has 170000 rows, so the vba code needs to be fast.
AU formula is: =VLOOKUP(A2,$BA$2:$BC$180000,3,FALSE)
AX formula is: = IF(AN2 = "Test1","To be adjusted",
IF(OR(AN2="Test2",AN2="Test3"),VLOOKUP(A2,$BE$2:$BF$180000,2,FALSE),
VLOOKUP(A2,$BA$2:$BC$180000,2,FALSE)))
My production spreadsheet has thousands of unwanted columns and I am unsure as to how they appeared. I want to delete them. I have tried selecting all of them and deleting them, but nothing happens. I have tried go to > blanks, but it freezes because there are so many columns.
Also, I tried to copy paste to a new sheet, but the formulas get messed up.
For a mental picture, the spreadsheet is approx 225 rows by 25 columns.
how do I transform thousands of groups of columns (each group has 6 columns) into A Single row (placed into another sheet) using Macro?
Since my data is large, so efficiency is also important.
Basically I want to be able to type a number and have a colon inserted two spaces to the left. So 123 would become 1:23, 1234 would be 12:34 and 12345 would be 123:45.
I'm working with anywhere from one to several thousand hours in a spreadsheet. I'd like to be able to use a time mask like this:
i want to know how to numberformat numbers (Currency) into thousands,lakhs.
For example i m having amount in colmun D like 1239.00 i want to format the numbers in this column as Currency with thousand seperator and lakh seperator. like1,239.00 or 1,23,900.0
I'm using the record macro function to make a code that is concerned with formatting information generated in excel by another software program.
My original idea was to replace the information in column F with the concatenated info of column F-L, but I couldn't get this to work either. So now I'm looking for a work around for my work around.
I can get the macro to work just fine with "Macro 1" below, but unfortunately when I use the entire column range, it populates thousands and thousands of rows below the (around 50) lines of information I have. However, the populated information in my sheet is completely variable - it could be 1 line or 5000 lines. These will also be printed, so I cannot just cap the range at 5000 because then it would print all of the blanks.
I tried several work-arounds after googling. I tried to leave the blanks and then delete them once they were populated - messy, and it crashed excel. I tried End(xlUp) and I tried using lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
how to only copy the formula in Column M to the last row of information (which would be the row with the last value in Column A).
MACRO 1:
Columns("M:M").Select
Selection.FormulaR1C1 = _
"=CONCATENATE(RC6, "" "", RC7, "" "", RC8, "" "", RC9, "" "", RC10, "" "", RC11, "" "", RC12)"
Range("M:M").End(xlDown).Select
Columns("M:M").Select
Selection.Copy
Columns("F:F").Select
[code].....
MACRO 2 (Only the Changes):
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Select
Range("M" & lngLastRow).Select
Selection.FormulaR1C1 = _
"=CONCATENATE(RC6, "" "", RC7, "" "", RC8, "" "", RC9, "" "", RC10, "" "", RC11, "" "", RC12)"
Range("M" & lngLastRow).Select
Selection.Copy
We have created a userform with several textbox for numbers. We'd like to show them with a format similar to "###,##0.00" and allow the users to enter numbers without commas, but always show the textbox formated. Is there any special way to manage this format? As the textbox is a "text" value, how can we manage the input and the numbers? Should we always replace the "," withi a nullstring? Example:
Private Sub tbCakPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
tbCakPrice.Text = Format(Val(tbCakPrice.Value), "######0.00")
tbPxCakCnFCgl.Value = Format(Val(tbCakPrice.Value) + Val(tbFleteRealCgl.Value), "########0.00")
End Sub.......................
How do I use a number formatting function that gives me the thousands seperator and no decimals?
View 1 Replies View RelatedI am working with a worksheet that has has 26 columns and about 2000 rows. However, somehow Excel has added tens of thousands of rows and columns to it, all of them empty. I know this is causing some memory trouble but I can't find an easy way to get rid of them. At one point I fixed the problem by selecting the cells I was using and starting a new worksheet, but I don't want to have to do that every day.
View 4 Replies View RelatedTo convert numbers (975,095.97) to thousands (975.1) without physcially changing each one?
View 2 Replies View RelatedI am trying to calculate weighted average cost for a list of thousands of items.
I will try to explain this as best I can.
Column A is item. Column B is Mode. Column C is Volume. Column D is Cost
There are only 2 Transmode Per Item. So each item is pretty much listed twice and I need to calculate the weighted average cost per item.
I know how to use the SUMPRODUCT function to calculate weighted average and could slowly go through the spreadsheet item by item calculating this, but is there a way to do this with some sort of formula? Similar to a Subtotal that would recognize each change item and calculate the Weighted Average at that change.
To display 1,100,000 as $1.1M & 110,000 as $110.0K I've used the below Custom Number Formatting.
[>=1000000] $#,##0.0,,"M";[
Question 1: how i can automatically add a cell into a formula by just clicking the cell to be added? in other words, say i typed an "=" sign in a cell and want to add a number of cells just by clicking the mouse without having to type the "+" sign after selecting each cell. the formula will just appear as > =A1+B1+C1 by default with each click of the mouse. Is there a default setting that can be set so the plus sign will appear with each click?
Question 2: how can i set a comma to appear for all thousands, (i.e. 1,000) without having to format each cell individually to show commas?? in other words that any spreadsheet i open will always insert comas for numbers larger by a thousand?
I am working on a excel sheet that tracks thousands of records. Basically, have 400+ employee's who have scores that need to be tracked. The scores are % based... to start we have 15+ columns but all that matter for these calculations is probably 3. We have score 1 which their required to hit 90% score 2 which is 80%, they are seperate scores. Some people dont count so we disqualify the formula from counting a 0. My current forumla is...
COUNTIF(K418,"<90")+COUNTIF(N418,"<80")-COUNTIF(N418,"=0") where the rows change for each record. We are counting the # of times they don't hit their goal. They can only be held accountable for one score a month not for both scores so created a 2nd column which has =IF(O418>=1,1,0) so if it came back with 2 counts it changed it to 1. OK, so background explained
Now the tricky part... we have a running total per month of how many times they did not hit their goal. We have a column with the mm/yyyy. So we have a pivot table which breaks down the associates by name department etc... and once completely drilled down into it gives a breakdown of their score for every month and then if they did or did not hit their goal by referencing the previous field I discussed. Currently it has a running sum, but I am stuck on the twist I need to throw in... if they meet their goal for 3 months straight, 1... we will call it "occurence" drops off. So for example.... Jan 1 Occurence, Feb 0, Mar 0, Apr 0. So in this case, Jan, Feb, Mar need a running total of 1. Once april hits, the running total needs to go back to 0. So if they got an "occurence" in May, the running total would go back to 1 instead of the current 2 my sum shows.
I imagine there has to be some way I can do this, but I have been thinking about this and hit a brick wall. Any idea's at all? For reference column A is the mm/yyyy (which can be reformatted differently pretty easily). Column K is score 1, column N is score 2, those are the 3 fields I reference for my forumulas currently, the pivot table uses everything else.
Here is an example of one associate.... the top row is the average for the associate and the sum of "occurences"... I want to or plan to have a running total section added on the side however if possible. This associates running total should be 2 instead of 3 as there was a 3 month gap for Mar, Apr, May so it would have been -1 in May as explained above.
Doe, Jane103.7788.823
1/1/200998.5894.120
2/1/2009101.6270.591
3/1/2009110.61105.880
4/1/2009131.78117.650
5/1/2009102.0588.240
6/1/2009110.4658.821
7/1/200998.23105.880
8/1/200986.8270.591
9/1/2009100.3982.350
10/1/200 97.1694.120
I have a giant sheet that culminates in a graph. It is for various regions. When used in a country with in-control currency, you end up with a nice healthy number that looks good on the graph. When our Zimbabwe office uses it with their crazy inflation and high prices, we get a crazy number that goes into scientific notation...
Anyway, my question:
Given a number like 4,200,000 or 6,500 or 345,123, I want to be able to show as few trailing numbers as possible, and give a nice K or B or D or however you abbreviate Quadrillion (kM? for Zimbabwe) after it.
And, if possible, I'd like to do it with number formats, but I am flexible so long as it doesn't use VBA.
The lookup table would be:
Sheet1 AB1<1,000As is21,000K31,000,000Mil.41,000,000,000Bil.5>1,000,000,000,000Tril. Excel tables to the web >> Excel Jeanie HTML 4
All numbers have 2 significant digits, and I want to keep 2, so if it ends up as 4,200,000, I want the answer to read 4.2 Mil, etc.
I tried doing this with a lookup, and couldn't handle it.
I am trying to write a custom cell format that would allow me to replace the comma with a space after the thousand's place. Therefore, 1 million would look like: 1 000 000 instead of 1,000,000. I do NOT want to change my regional settings to accomplish this.
When I write ### ### ### it accomplishes this, however, there is one small glitch. When you apply an "underline" you get the following result:
_1
_10
_100
_1 000
_10 000
_100 000
1 000 000
10 000 000
100 000 000
The underline does not lineup evenly with the "ones" digit until we reach 1 million. I want to write a universal format in which the underline lines up evenly regardless of the number.
I want to show numbers on my excel spreadhseet in thousands. However, the challenge is I want to show negtive numbers in red within brackets. For instance, I have a number -104,784,089. I want to display it as (104,784) in red color. Similarly, I want to show 577,090 as 577. Is there a customer formatting I can set up in excel that do that?
View 2 Replies View RelatedI got a column of data in format as below:
Column 1:
ABC - AC123
ASD - DS335
BBC - KK311
SWT - TR735
TIM - DN849
Between two texts to be separate, there is a " - ", we can separate them with that symbol right?..The cells should be look like after separate shown belows:
Column 1:
ABC
ASD
BBC
SWT
TIM
Column 2:
AC123
DS335
KK311
TR735
DN849
What is the macro code to do this
