I have column with thousand of line with european currency sign at the end. The excel wont add em cuz these sign are not $ sign. How can I eliminate the sign at the end so that I able to add up?
View 9 RepliesI have a very frustrating problem with my business plan i am writing in Excel. every so often it crashes and when I open it again all the £ signs have turned to $ signs and i have to go through and painstakingly change them all. This also happens even if I just close the file carefully and open it up again. I have tried looking in preferences to see if there is a default setting but can't seem to work it out. can anyone help me with this tiresome issue?
View 9 Replies View RelatedI have a spreadsheet with cells defined as Euro & Dollars. I want to add an automatic conversion so for example, if a cell is defined as $ the formula will return the sum X 4 and if it is Euro than sum X 5.
I tried to use "if" combined with "search" (so if the cell contains "$" multiply by 4) but since the cell is defined as currency the $ sign does not appear and cannot be searched…
I have a file contains thousands of rows of purchasing order. the purchasing value is in different local currency,the data(number) format is "Accounting" .
Is there a way to separate the currency sign and the number into different column?
I need to the currency sign to be able to convert data to desired currency. But Excel read the data as number. so I was doing it row by row. Such a pain and not efficient.
i have lets say a table with rows that contain the following data.
SKU - PRICE - SALEPRICE - DESCRIPTION - SALEDESCRIPTION
What I am trying to do, is to make a concat formula that says:
SALEDESCRIPTION = DESCRIPTION+price was+PRICE+now+SALEPRICE
In essence so that i can simply pull down the formula, and all our product descriptions have 'special offer, was xx (price) is now xx (sale price).
This works fine, except that in the SALEDESCRIPTION, the amounts from the price and saleprice fields are being brought over without decimal places and without currency symbol.
I have tried formatting those two columns as currency or accounting but neither works.
looking for a formula to remove an "= sign" at the very beginning. hence if "=Jordan" then want to change it to "Jordan" in cell T3 only.
but wait, the resulting cell T3 "=Jordan" already has a formula inside it =(U3&V3&W3&X3&Y3)
How to enter formula for removing "= sign" and not disturbing the existing formula ....
How to make EXCEL 2007 Automatically convert all my CURRENCY symbols in all cells formatted for CURRENCY in THE SAME SHEET to the one and ONLY currency selected in JUST ONE cell ? (i mean, after conversion, all of them are Not just display currency symbols, but Calculable monetary values like regular Excel's built-in currency format)
I am a dummy in VBA so I really am not familiar at all if the solution is really one of VBA. I don't even know how to start programming VBA at all in Excel.
I have several rows which lists different currencies - USD, GBP, EURO, JPY etc.,
A1 usd 5000
A2 GBP 7000
A3 USD 3400
A4 EUR 12000
A5 JPY 89000
A6 EUR 1000
At the bottom I want to show the total of each currency separtely.
I'm trying to solve a strange problem in a piece of code.
I have a variable that is define as Double called STD. When i try to insert that variable in a formula the decimal sign (for me a comma "," because I'm Portuguese) gets converted to ";" (which is for me the separation sign for the expressions in excel formulas. ex: AND(A1>0;B1>0)=TRUE). The code is:
My boss uses the + symbol and the = symbol in his formulas eg "=+E3*E4" What is the advantage or difference in this as to just using "=E3*E4"
View 3 Replies View RelatedIn a formula, what effect does putting a plus sign after an equals sign? e.g.
=+((1+B8)^12)-1. I orginally assumed that it made sure that result the would always be positive but I was wrong.
An in-house server spits out a whole lot of dates which I put into Excel and then use the dates for analysis (VBA code). The problem is that Excel is getting confused about the date formats. Sometimes the dates are formatted as dd/mm/yyyy and then the next line can randomly be mm/dd/yyyy. This totally messes up all changes of having data integrity.
View 5 Replies View RelatedI get sent a spreadsheet by one of our partners where the dates look like they are in European date format except thay are actually in American format.
example
01/09/2007 - they mean 9th January 2007 but Excel holds 39326 which is 1st Sept 2007.
I can obviously format them but when I sort the list Excel still reads 1st Sept instead of 9th Jan
There are over a thousand entries so re-typing isn't an option.
I have a userform which is used to update a spreadsheet.
There are 7 textboxes which add dates to the spreadsheet. These dates are then used in formulas.
Every time a record is saved the date format changes.
Ths means that if the date is entered dd/mm/yyyy it writes mm/dd/yyyy and this is fine. When this record is next retrieved it will show mm/dd/yyyy and any new date will be dd/mm/yyyy.
Wen saved to the spreadsheet both formats change meaning the dates in the spreadsheet appear in diffrent formats messing up all formulas.
How can I stop the date format changing each time the record is saved?
I am trying to get the user to enter a relevant date but code returns date in US format. That is mm dd yyyy instead of dd mm yyyy.
View 5 Replies View Relatedthere are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.
I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.
I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another
18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK
Having trouble applying a name to the line
For Each ctrl In Form1.Controls
The name of my Form is "frmLayout"
How can I change Form1.Controls to "frmLayout".Controls ?
don't tell me just to type in it without the quotes, I mean when the name is in a string variable.
is it possible to display the value in a cell with a £ and also make it automatically go red when the sum is negative?
View 9 Replies View RelatedHow can I add "$" sign in multiple cells such as:
=D7
=E7
=F7
so result:
= $E$7
= $D$7
= $F$7
A solution would be to use the "F4" every cell, but the process requires more time.
I tried to select multiple cells and press "F4" but do not works.
Why when I convert the following data, why the plus sign "+" converts to a /K?
I'm trying to convert values in groups of cells to a 3 of 9 barcode.
The value would be something:
+R4235
But when the font is changed to a 3 of 9 barcode and then I test the barcode in Notepad I notice that the plus sign is replaced with a /K.
I want to save phone no as +99 9876543210 in excel 2003 on my xp pro machine. But if i give a + sign in the cell, some blue dotted rectangle shows up and everything messes up.. I think it is treating it as a formula or something... how can i save this in the cell. tell me in detail if you are going to tell me about macros or vb code as I don't know how to insert code or program macros.
View 3 Replies View RelatedI am a new user both to Excel and these forums, but I am very glad they both exist! What I have is this (it is C13 for those playing at home):
View 4 Replies View RelatedI need a formula that reads If a number is less than zero then it will show as positive, but if the number is greater than zero then it will show as a negative.
View 6 Replies View RelatedI got three columns in excel. Column A is a list of items, so is column C. Column B displays the similarity in the 2 lists.
I use the following command =IF(ISERROR(MATCH(A1,$C$1:$C$265,0)),"",A1).
SO:
A B C
Cat Fish
Dog Dog Roll
Bull Carpet
Apple Dog
But what I want to do is the following:
If I have "D-og 12 2-3" in column A and say "do g 1 22/3" in column C, I want it to be displayed in column B. To do that I need to decapitalise and remove all the special symbols such as space, ), (, *, &, ^, %, $, #, @,! etc.
So I get dog1223 in column B since they are the same.
I have columun in one row, if got sign (-) want convert to value 8,4,2,1 and this value convert to hex code...please anyone give guideline. thanks in advance.i has attach file please have a look.
View 9 Replies View RelatedBeen struggling with the + sign. I have been using code such as shown below, but it just wont work for +.
Selection.Replace What:="~~", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="^", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="~*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
i want formula in b1 to get the formula in a1 without Equal Sign
A1=(A2*A3)+2
Reults B1 should be (A2*A3)+2
I have my results in cells B2:B55 consisting of three signs which are 1, X and 2.
AB1DateResult218-08-131325-08-132401-09-13X508-09-132615-09-132718-09-132822-09-13X925-09-1311029-09-1321102-
10-13X1206-10-1311313-10-1321420-10-1321523-10-1321627-10-1321730-10-13X1803-11-1311906-11-1312010-
11-1312117-11-13X2224-11-1312327-11-1312401-12-1312508-12-13X2611-12-1322715-12-1312818-12-1312922-
12-1323005-01-14X3112-01-14X3219-01-1423326-01-1423402-02-14X3509-02-1413616-02-1413719-02-1423823-
02-14X3926-02-1424002-03-14X4109-03-1414216-03-1414323-03-1414426-03-1414530-03-14X4606-04-1414713-
04-1424820-04-14X4927-04-1425004-05-1425111-05-14X5218-05-1425325-05-1415401-06-1415508-06-1415611-07-14?Sheet1
Is there any way the next falling sign can be predicted in cell B56? And if so, what would be the measures to guess the next sign?
I'm trying to simplify a section of my code by naming a long string that is repeated. I tried by simply place the Name before equal sign (=) and also putting Set before the name. Both failed though I've seen both methods used successfully in other code. Am I having a syntax issue of some sort?
Private Sub ConditionalRowDelete()
Dim NumRows, iLine As Integer
ActiveSheet.UsedRange.Select
NumRows = ActiveSheet.UsedRange.Rows.Count
Dim CircuitType As Range
Set CircuitType = Range("C" & iLine).Value
For iLine = NumRows To 2 Step -1
If Range("A" & iLine).Value > "6999" Then
Rows(iLine).EntireRow.Delete
End If
Next iLine
For iLine = NumRows To 2 Step -1
If CircuitType = "VOIP" Or CircuitType = "Customer Care" Or CircuitType = "Dialup" Or CircuitType = "IRU" Then
Rows(iLine).EntireRow.Delete
End If
Next iLine
End Sub