Code Converting Text To Exponential Notation
Dec 26, 2009
I have a bit of code that is grabbing only the first 9 positions of an alpha-numeric string. The problem I am running into is that when the alpha-numberic value contains an "E", the code turns it into an exponential number in error.
For example, I have the following in column G: 01877E107000. When I run the code, it turns into an alpha-numeric value. The result should be 01877E107 (in column K). I tried using the text function within the code.
View 3 Replies
ADVERTISEMENT
May 1, 2007
I was wondering if anyone could tell me how to do away with the "Exponential" appearance of numbers (in Excel) when they are formatted as text. I am working with National Stock Numbers and there are no required computations based on them being formatted as a number ... AND ... one of my constraints is that they are required to be formatted as text when imported into an Access database ...
View 5 Replies
View Related
Mar 13, 2013
I have a large file with account numbers in Number format. Before I can import the data into a database the account numbers need to be converted to Text. I use Text(A1,0) to do this in a large file. The data then gets copied into a new Import file. When the data gets copied the account cell reverts back to scientific notation for the long account strings. The import file account column is formated for Text. The only fix is to then double click on each account that is in scientific notation to convert it back to text.
Is there another text formula I can use?
View 1 Replies
View Related
Sep 27, 2013
I have two columns that read:
XXHSXX HR
XXTRXX MG
XXXFXX XG
XXHSXX HS
XXTRXX MG
XXMEXX RU
XXHSXX HR
XXHSXX
XXTRXX
XXTRXX
....
The above list represents 2 columns. The left column is 400 lines long. The right column is partially complete with 2 letter codes that represent the 6 letter codes on the right. I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text? I don't want to have to manually type in 2 letter codes that match up the left column. What do I do instead?
View 2 Replies
View Related
Nov 10, 2011
I have a working formula that I want to convert to vba code... I only want to store the formula result in each cell in my range, not the formula itself. It is an ARRAY formula (entered with CTRL + SHIFT + ENTER) and it would be ideal if it could follow R1C1 format to keep it relative for each cell.
Here is the formula as it's entered in my spreadsheet:
=INDEX(PerfMetricTbl!$U$2:$U$250000, MATCH(1, (PerfMetricTbl!$A$2:$A$250000=$A2)*(PerfMetricTbl!$D$2:$D$250000=$D2)*(PerfMetricTbl!$Q$2:$Q$250000=$Q2)*(PerfMetricTbl! $R$2:$R$250000=Z$1), 0)))
I am not super familiar with using INDEX/Match combos which is why I am struggling with this one...
The end result should be:
For each cell in myRange
cell.value = 'Formula result here
Next cell
-OR-
If possible to fill the range in one step:
myRange.value = 'Formula Result Here, utilizing R1C1 relative reference... (this may be a stretch given the range is 55krows x 18 columns)
The problem at hand is I need to fill a range of approx. 999,000 cells with values and the values are a VLOOKUP with 4 matching criteria. So returning the value from Col U on my LookupTbl when the criteria in Col's A, D, Q, and R are an exact match to my values in A, D, Q, and Z:AQRow1.
View 2 Replies
View Related
Apr 24, 2009
Converting formula to static code. I am currently using the following code on a spreadsheet:
View 3 Replies
View Related
May 12, 2009
Wondering where this code can be adjusted to be much more efficient. Right now it is going through 1000 rows and 14 columns and it is taking about 10 minutes. I eventually need it to go through 6000 rows and 32 columns..
I already stored the HexToDec(Cells(x, 1)) result to variable H2D and it didn't really make too much of a noticeable difference.
View 14 Replies
View Related
Mar 27, 2014
I'm trying to convert my nested if statement below into vba code.
=IF(L27=15,"15+", IF(AND(L27>=5,L27
View 1 Replies
View Related
Jun 24, 2008
I created this code in Excel 2007 now i want to put it on my other users machines they are using Excel 2003 and i keep getting debug errors can someone help
Sub setup()
' Keyboard Shortcut: Ctrl+Shift+C
'add sheet
Sheets.Add After:=Sheets(Sheets.Count)
'format headers
With Range("A1")
.Value = "Job Name"
.Name = "Arial"
End With
With Range("A2")
.Value = "Quote #"
.Name = "Arial"
End With
With Range("A3")
.Value = "Job #"
.Name = "Arial"
View 9 Replies
View Related
May 21, 2006
I have very little experience with Excel. What I am trying to do is to convert a timecode in this format 00;22;21;00 that is 22 min 21 sec and 00 frames to a serie of numbers that I have hard time to figure out. Example
00;22;21;00 = 13410000000
I want to be able to paste the timecode from premiere 2.0 (00;22;21;00) into a Excel cell that would automaticly convert it to (13410000000) so that i can eventualy generate a .txt file that would look like this:
<Scripts >
<Script Type="URL"
Command="http://www.softfx.com/slide01"
Time="13410000000" />
</Scripts>
View 2 Replies
View Related
Jul 6, 2006
I'm having a slight problem as I cannot find a way to translate the following code from my worksheet formula to VBA: =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=1,A1,MID(A1,1,FIND("-",A1,FIND("-",A1)+1)-1)). The code takes a value from one cell and takes the part that I need. There are two main types of inputs, they are in the form:
aaa-aaaaaaa-aaaaaa
aaaa-aaaaaa
The lengths are variable, but for the sake of showing you an example I have simplified it to the previous. The formula takes the part of the string that is before and after the first hyphen and puts it in the cell next to it.
View 6 Replies
View Related
Apr 25, 2008
I am working with a program that creates numbers as follows:
Instead of -2.74E-6, the program writes -2.74-6. The E is left off to save space.
When I import this data, Excel automatically "does the math" so the cell reads: -8.74 instead of -2.74E-6.
View 9 Replies
View Related
May 18, 2009
My question is about converting a Literal String to Character Code. I'm using the following coded InputBox. And it prompts the user for what characters to search for in a string.
View 4 Replies
View Related
Feb 27, 2014
I have the following data
A1 - 65.23
A2 - 1923.52
A3 - 945.18
A4 - 182.54
In column B
B1 =exp(A1)
B4 =exp(A4)
both are fine, however
B2 =exp(A2) - it is showing #NUM
B3 =exp(A3) - it is showing #NUM
Same case for all larger numbers
View 1 Replies
View Related
Jun 18, 2008
I have inherited a series of data relating to a change in a specification over a period of time and a number of cycles.
See attached.
There is already a chart which shows the data and has an exponential line.
I want to find the value of Cycles where the Average Flash exponential from the chart line is 0.131.
FYI this is to plot deterioration in a piece of tooling, 0.131 being the accepted warning level. If you feel there is a better statistical model to use for that application then I'm all ears!
View 9 Replies
View Related
Dec 12, 2013
I have a frequency table (attached) that I need to be able to interpolate values between the known values on something other than a linear basis (e.g. exponential or logarithmic).
View 5 Replies
View Related
Apr 16, 2014
interpolating numbers in excel for an exponential function. I want to know how to be able to curve a curve in 4 different ways essentially the 2 that make up the two halves of a peak and the 2 that make up the two halves of a trough. I've tried using the LINEST function to create this interpolation and it's good to get 1 of the 4 curves I'm interested in. how to do the other 3? I'm also interested in knowing if these functions "whip the tail up" as in almost like how the tail end of a "sin" function flips back up. here it is.
[URL]
For this example of the exponential function, it creates the curve in one particular direction. I was wondering how I would do it to have it curve in another direction, for example I was testing the same function, but going downwards, and also having the tail curve back up (by hump I mean to simulate a gaussian distribution with the tails) how would I do that. seems like I now have part 1 of 4 aspects of the curve. here's the attached file to show what I mean. I want to get the hump going up and then sort of reflected along the y-axis and then similar features if I were to reflect the curves on the x axis. is it possible with the functions in excel? Linest test.xlsx
View 14 Replies
View Related
Jan 28, 2014
In excel if it changes number to exponential format it would default to, for example if number is 12345 -> 1.2345e4, 0.012345 ->1.2345e-2
I was wondering it is possible to which a function to change this default format ie for example 0.012345 -> 12.345e-3
I basically want this kind of formatting as its would display information in terms of milli, micro, pico (or the opposite side kilo, mega, giga and so on).
View 7 Replies
View Related
Dec 16, 2008
I have some short code that imports data and account numbers that is "|" delimited. The problem I am having is that during the import, excel converts some of the account numbers to exponential numbers.
e.g. it converts 542690E24 to 5.4269E+29.
Is there a way to avoid this from occuring during the import? I tried to format the destination cells as '000000000' (all accounts are 9 digits) but it didn't work.
View 6 Replies
View Related
Jul 25, 2006
to write a function that will exponentially weight a series of returns by a certain decay factor.
I have excel spreadsheets that can do this over several columns, like this
series of returns... rtns squared
2.50% =2.50%*2.50%
3.50% =3.50%*3.50%
5.00% =5.00%*5.00%
1.01% =1.01%*1.01%
0.75% =0.75%*0.75%
4.01% =4.01%*4.01%
-2.78% =-2.78%*-2.78%
View 5 Replies
View Related
Jun 5, 2009
I dont know why this wont work
it falls over on the ".Columns(6).FormulaR1C1=" line
View 14 Replies
View Related
Dec 5, 2009
I've been working on a ss that requires me to create a function to determine a value from a exponential curve where the area of the curve is variable . The terminal points, 0,0 and 100,100 remain constant and the curve is symmetrical at the 45degree angle. How can I derive a function knowing only these things.
Here is an image to crudely illustrate what i mean [url]
The area can be 10, 20, 35% it doesn't matter, I need a way to determine the function from just the area!
View 14 Replies
View Related
Dec 6, 2013
I am trying to find the exponential equation from a set of data in excel. The problem is that the graph does not follow the scatter plotted points on the graph. How would you remedy this?
View 1 Replies
View Related
Feb 1, 2013
I have an excel file that contains data imported from a csv file. One of the columns contains text strings that I would like to convert to a different text string. The column that I want to convert has text data that is similar to the following format:
BL BLDO
BLDO
BLDO
BLDO
EL BLDO
Now, the data above should look like this:
+CONC
CONC
CONC
CONC
-CONC
The "+" should replace the "BL" in the first row. The "CONC" should replace the "BLDO" in all rows. The "-" should replace the "EL" in the the bottom row. Within these files that I work with, there are perhaps 60 or 70 unique text items that each have a corresponding text item that should replace it, and the files usually contain upwards of several hundred to one thousand rows of data. In other words, I have a list of 60 or 70 text strings that have a specific replacement text string...and I need to apply that conversion to several files each day.
View 1 Replies
View Related
Mar 21, 2014
Similar to this thread: [URL]
I'm trying to come up with a VBA solution to.... Sigma from j=0 to i of: L((i-j)/i)*b*t.
View 7 Replies
View Related
Mar 16, 2009
I am currently trying to find an easy way to convert cell references to number format. The issue I'm having is when compiling a small formula (CONCATENATE) as part of a macro in order to produce a unique reference number to a column of data (1000's of cells long) I come accross the age old number stored as text issue after the macro has converted the formula to values.
Have tried editing the macro to number stored as text = false which removes the annoying little red triangle but the vlookup functions still wont read the values. My current solution is to highlight the entirity of the data and convert to number which takes a long long time as there are multiple lookups assigned to the cells, which for my current project is not acceptable.
Am looking for a quick solution, (with no interaction required) something that would convert the cells to the format required either before the formula is assigned and overwritten by values or just after. Currently I'm stumped.
View 3 Replies
View Related
May 29, 2009
I need to convert my percentage marks into values based on competency values. Once again my school board has change the final reporting method. Last year we had to use A, B, C, D. This year it is based on scales of 1- to 5. Since excel cannot have more than 7 nested IF statements I was wondering if anyone knew of a way to have a percentage mark converted into a scale number. Below is the following data.
0-5% = 1-
06-15% = 1
16-25% = 1+
26-35% = 2-
36-45% = 2
46-55% = 2+
56-59% = 3-
60-64% = 3
65-70% = 3+
71-76% = 4-
77-82% = 4
83-88% = 4+
89-94% = 5-
95-00% = 5
View 4 Replies
View Related
Mar 5, 2011
I need to build excel sheets and be able to convert images to text. I copy the sheet from a web site and all fields in the table come in as text except for one. That field can be 1 of two images. Right now i have to save the xl sheet as html and then Find/Replace the html image code with a + or - sign which is time consuming. Is there a way I can convert he image though excel. I need to be able to sort the sheet as well as pull the data into a separate tab.
View 4 Replies
View Related
Jan 24, 2013
I am receiving a monthly breakdown of postage costs by department but the values are received in a text format of 1 234,56.
I need to convert these to numbers.
I have replaced the comma with a full and tried to use =substitute(cell ref," ","") to remove the spaces in those numbers exceeding £1000 but this does not seem to work.
Postage.xlsx
View 3 Replies
View Related
Jan 25, 2013
I have a formula which extracts a number from a text string.
The text string is in A2 - the formula which extracts the number is in B2
However - when I copy the data in B2 and paste values to another sheet / cell - the forumla is as text - and I get the little green corner symbol and I have to convert to number.
Some VBA code - that will automatically convert my extracted "number" - to an actual number in number format.
View 8 Replies
View Related
