# 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
Sep 25, 2013

I have imported data that is showing dates as 18th Sep 2013. Excel does not like this and I have tried various methods of conversion but nothing seems to work. I will be getting data that has Oct, Nov, Dec etc so the formula needs to be able to handle these too. I would like these 'dates' converted to excel friendly dates.

View 7 Replies
View Related
Apr 20, 2009

How can i convert text columns to numbers in VBA.

View 8 Replies
View Related
Apr 23, 2007

i am having a problem here,i Have got two columns one for item name other for item quantity.the quantity field has data like:

2+2+8

5+5

2+9

5

666+98

258

6+89+7+4+2+4+5

.....etc.

i need to calculate the total of all the cells like the first has sum 12,second has 10 ,fourth has 5 .Now i can get the sum by simply putting a " = "

in the start of the cell but the problem is that ive got 8000+ cells putting = manually will kill me.

View 9 Replies
View Related
Mar 4, 2003

On my Excel spreadsheet, I have a TEXT value in A1 that is all numbers, i.e. "000065200". I would like to write some VBA code that changes A1 to the numeric value . How do I do this?

View 9 Replies
View Related
Sep 26, 2011

I have the following SQL that works fine...

Code:

= "SELECT [ID] FROM [Data Connection TBL] WHERE ([DateSentYear]= ""1992"" AND [DateRcvdYear]= ""1992"");"

However, if I create loops to cycle thru the years, the following doesn't work?

Code:

= "SELECT [ID] FROM [Data Connection TBL] WHERE ([DateSentYear]= " & CStr(x) & " AND [DateRcvdYear]= " & CStr(y) & ");"

Is CStr not right?

View 2 Replies
View Related
Oct 25, 2011

I have the following formula that pull a value from anotther worksheet. However, for some unkown reason it does not recognise the value as as number

=OFFSET('ECM & Volvo'!A474,0,Journals!$D$1-1)

I would like a formula to convert the value into a number

View 3 Replies
View Related
Dec 18, 2007

I have few dates in column C. My Column B is blank. I want to convert all text in column C in proper date format in Column B.

The dates entered in column C is in this format 05.11.2007 (dd.mm.yy format) Is there any formula to convert it in proper dates in same which will show the date in 5-Nov-2007 format?

View 9 Replies
View Related
Aug 21, 2008

I have a column (R) that is formatted as text. Each cell represents a time in military format.

How do I convert the values in the column to be in 00:00 (hours:minutes) format instead of text?

For instance, 1200 is the value in R2. I want it to show as 12:00.

I tried the following:

1. Create a new column (S)

2. Format the new column (S) as 00:00 format

3. Copy cell R2 (whose value is 1200) and paste into S2.

Result: S2 value is the same (still 1200). Formatting in the S2 cell changed from 00:00 format BACK to text format upon me pasting.

So I tried and changed it BACK to 00:00 format, and I get 0:00 as the new value in cell S2. Also, the function bar shows "4/14/1903 12:00:00 AM" for cell S2. I need a formula for this apparently,

View 9 Replies
View Related
Mar 4, 2003

on my Excel spreadsheet, I have a TEXT value in A1 that is all numbers, i.e. "000065200". I would like to write some VBA code that changes A1 to the numeric value .

View 9 Replies
View Related
Apr 29, 2006

I need to convert some text to a number. I was wondering if there is a way to do it in Excel. Like, for example, the date 3/14/2006 can be converted to 38790. Is there any function in Excel that can convert a word to a number?

Example would be (numbers are totally random)

md_bla_bla_02 would convert to something like 3452615261502

View 9 Replies
View Related