Exponential Number (formatted As Text) Appearance

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

ADVERTISEMENT

Join Text With Formatted Number

Jul 2, 2008

I had a hard time to put a suitable title to this query, not sure if above is good enough for my query. I am entering combination of text and number in a cell using a formula. I am wondering if it is possible to add formula which can format number such that it is displayed in accounting format. Example:

Expected output = Trial 1,000
Using this formula
="Trial "&Sheet1!A1
where A1 in Sheet1 = 1000
Output is Trial 1000

View 6 Replies View Related

Determine Whether Cell Contains A Number Formatted As Text

Jan 2, 2009

How can I interrogate the contents of a cell to determine whether or not it contains a number formatted as text? I intend to run a .value = .value over each such cell to convert it to a number format, but I only want to do this to relevant cells.

View 9 Replies View Related

Excel 2010 :: Return Result For Appearance Of Text String In Any Cell In Row?

Jan 30, 2014

I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.

I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.

View 7 Replies View Related

Stop Hexadecimal Number Displaying As Exponential & Convert To Decimal

May 31, 2008

I use hex2dec frequently and input with a hex 'E' such as 05e8 is misinterpreted as exponential notation. I can use =hex2dec("05e8") to get the correct value, but when the hex number is in another cell, I get the exponent assumption/ conversion (to 5 x 10e8), or a # NUM error if it is in quotes. This seems like a blatant problem but I've found no mention of it in searches or FAQs.

View 3 Replies View Related

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 View Related

Paste Special Options Limited To Formatted Text Or Text?

Mar 25, 2012

I have recently found that when I copy ranges (usually containing formulas), I only have the option of copying these ranges as text ( or vales). I can no longer copy formulas , (or formats, col width etc) in my excel worksheets.

This problem has only recently occurred and applies whether i have one or many spreadsheets open.

View 6 Replies View Related

How To Sum Numbers Formatted As Text Ignoring Text In Cells

May 30, 2014

I have a spreadsheet where i do need to count cells values that are formatted as text but i do need to ignore the real text in some cells, in this formula i do need to add values depending of the adjacent cell.

I have attached a example : New Microsoft Excel Worksheet.xlsx‎

View 8 Replies View Related

Dates To String But Cell Formatted As Number

Feb 11, 2014

I'm working on a sheet for our accounting section which has been in use for quite some time, which means, that I'm not supposed to change a hell of a lot in it since it's accounting and everybody is afraid of changes.

Coming to the point, I have the following problem:

(1) I have a date.
(2) I need that date in this format: "mm-yyyy", written in a certain cell.
(3) BUT: the cell should NOT contain the date itself, but JUST the text (some tables in the background need this format)
(4) Changing the cell to text-format is not eligible, since it puts an " ' " in front of the numbers.
(5) The cell should be formatted as a number.

So, in conclusion:

I need a string/number with the date in this format: "mm-yyyy" in the cell formatted .NumberFormat = "0".

I'm working on an existing workbook...

I decided to add some code and a screenshot. This code will return the number, which lies behind the date, and write it into the cell. So that's not what I want, but maybe the code clarifying what I need anyway.

The screenshot shows the wanted output : screenie.png

View 14 Replies View Related

How To Determine If Number Formatted Cell Is Empty And Not 0

Feb 21, 2014

if the active cell is formatted as number and I write

x = Cells(Row, Column).Value

and the cell is empty or blank, x will be set to 0.

How can I determine that the cell is blank and not actually 0 ?

View 9 Replies View Related

Copy Formatted Text As Well?

Mar 30, 2012

I have written below code

Sheet1.Cells(1,2)=Shett2.Cells(2,1)

and my sheet1 cell has a value which is bolded and after running above code the sheet2 cell is displaying unbolded value

how to copy the format as well

View 4 Replies View Related

Sum Text Formatted Numbers

Jan 10, 2007

Is it possible to sum cells that contain numbers converted to text? I used TEXT function to convert them because I needed to format them (to display three decimal digits, if number < than 0.05, three digits otherwise). But now SUM Formula doesn't work.

View 4 Replies View Related

SUM Returning 0 - Calculation Set To Automatic And Cell Formatted As Number

Dec 19, 2013

I have this problem where the SUM function is returning 0. The context is this- I am simulating values using RAND() and looking up the values corersponding to probabilities from a different tables. I then use the TRIM function to return the value I need, and the final number is of a "general" format.

This is the formula in the column I wish to sum:

And this is dragged down the column.

When I change the cells using =VALUE(cell) the SUM function then returns the required value. Oddly the SUMPRODUCT function seems to work.

Calculation is set to automatic and the cell has been formatted as a number.

View 2 Replies View Related

How To Make All Numbers Formatted As Text

Apr 3, 2014

I have two columns with entries consisting of numbers. I'm trying to match between the columns but some names dont match because of the formatting.

Example

065 matches 065 (I have that green triangle on the cell that indicated "The number in this cell is formatted as text")

However 120 doesn't match with 120 (only one of them have that green triangle)

I tried to format all the cells as text at it seems the match only works when the green triangle is available. The green triangle seems to only show its self when I double click on the cell. I have thousands of entries so that doesn't work. I also tried to use the "Text to Columns" but it coverts entries like 002 into 2 which I dont want.

View 7 Replies View Related

Assign Value To Text Formatted Cell

Dec 30, 2008

I want to be able to "count" apples and oranges. Is there a way to record a particular text in a text formatted cell and count it. e.g. 10 cells, 3 say "apples", three say "oranges", 4 say "plums". I want excel to keep track of the three types of fruit when I change them and give me a running number of each.

View 2 Replies View Related

Find And Replace Formatted Text

Jan 6, 2009

I want to find strikethrough text and replace it with blanks. In my sheet there are cells that contain both strikethrough and normal text. I tried using the 'Find and replace' tool, specifying the format. I've attached a picture with the settings from the Replace window.

The problem is that Excel finds the cells that contain strikethrough text, but replaces with blank ALL the cell content. I would like to replace only the strikethrough text from the cell and leave the normal text as it is!

View 4 Replies View Related

Graph Data Formatted As Text?

Nov 29, 2013

I have a graph that pulls data from a toggled list. Values include currency, percentages and general numbers. The data is set up as text to report as currency, percentages and general numbers. Is it possible to have a graph read these text values?

View 1 Replies View Related

Formatted Currency Text In Formula

Mar 18, 2008

I am in need of a formula.
I am subtracting one number from the other and if the result is negative, “Short” otherwise “Add”, I want to use subtracting result in the formula. So for example A1 has 50,000 and A2 has 40000 so the formula in cell A3, should say add $10,000.

Or something like this, =IF(A1-A2>0,”ADD”,”SUBTRACT”,”&TEXT(A1-A2,”$#,##0.00”)

View 9 Replies View Related

Lookup Numbers Formatted As Text

Sep 17, 2009

The solution below to look up numbers in an array formatted as 10 characters as text.
=VLOOKUP(TEXT(A1,"0000000000"),LOOKUPTABLE,2,FALSE)

This has worked well except now I have received the data and the text I want to lookup has been reformatted (previously leading zeros) to the number with trailing spaces, still a total of 10 characters but the above formula no longer works. Is there an easier workaround other than using "Find" to locate the position of the first space.

View 9 Replies View Related

Command Button Appearance

Jun 23, 2007

I have a Userform with one RefEdit control and three CommandButtons. The RefEdit has the focus. But one of the three command buttons is the default (it varies). I would like to make the default button look like it has the focus (even though the RefEdit does). How can one hightlight a command button? I'm currently using underline, but that is too subtle.

View 5 Replies View Related

Conditionally Formatted Cells Which Turn The Text In Them To Red If The Value Is Less Than 40

Aug 18, 2009

I have a series of conditionally formatted cells which turn the text in them to red if the value is less than 40. This works fine.

Occasionally however a value of less than 40 will need to be entered along with the letter 'v'.

ie. 39v

I'd still like this to be coloured red, but it's obviously coming out as black.

Is there a way to sort this out?

edited to add : Im actually using a separate cell to enter the value 40 (as the value can change).

View 7 Replies View Related

Change Ddd Formatted Cell To Text String

Feb 17, 2012

How to change a "ddd' formatted cell and change it to a text string (even if you have to use another set of cells)?

I have 3 columns of cells A,B,C.

Column A has dates in it.

Column B has the "ddd" format of the A cells (takes date from A cell and turns it into the day of the week...ex:2/17/2012 into "Fri")

Column C is the one I want to formatt whatever is in the B column into a string, such as "Fri" but with it being text instead of "ddd" formatted.

View 4 Replies View Related

Convert Numbers To Specifically Formatted Text?

Feb 6, 2014

To input high volumes of dates I change the number format of cells to "00/00/00" so I can key mm/dd/yy without having to hit the slash key. In order to convert these values into an actual date, I've been using the =TEXT function because it allows me to format the text as "00-00-00" which preserves the numbers but inserts a hyphen between each set. From there I use text to columns to break each pair into its own column, and use the =DATE function to combine all 3 into a date.

Is there a way to automate this conversion process in VBA? Alternatively, I'd be open to a different method to input dates in the mm/dd/yy format without having to press the slash key.

View 2 Replies View Related

Can You Delete Specifically Formatted Text From A Cell

Jan 28, 2009

Is it possible to delete the work 'Strike' purely based on it's formatting I.E: as having a strike through set against it?

Im thinking it's not! I know you can use 'Find and Replace' for single cells with single words in, but not out of a sentence?

View 9 Replies View Related

Text And Formatted Date & Time In Cell

Mar 19, 2008

I am trying to get a single cell to display the following:

Last Updated: 3/18/2008 15:08 (GMT+2)

Entering =NOW() in a cell displays the date and time as required.

But entering ="Last Updated: "&NOW()&" (GMT+2)" displays the date and time as a serial number. Formatting the cell to Date does not change the serial number to date and time format.

The only way I have found to get the desired result is to use =NOW() in another cell (F13), format that cell to general to get the date/time serial number, then use ="Last Updated: "&TEXT(F13,"m/d/yyyy h:mm ")&" (GMT+2)" in the required destination cell.

As I said this works, but it strikes me as an inefficient method. Is there a formula I can enter or formatting I can apply to get the desired result without using an addition cell?

The result needs to be in a single cell. Splitting text and date/time into 3 adjacent cells will not work with my worksheet setup.

View 9 Replies View Related

Different Long Numbers Formatted As Text Are Considered Duplicates

Jan 9, 2014

I have an issue with long numbers (16 digits). To display them correctly in excel I formatted the cells as text. This part works fine, but now for some reason conditional formatting marks different numbers as duplicates.

In my case 3081281170122602 and 3081281170122601 are considered as duplicates by excel.

See the file attached. duplikaat.xlsx

View 4 Replies View Related

Identifying Numeric Values Within A Range Formatted As Text

Dec 31, 1969

how to convert number into text (acutal formating)

Eg. : 150500

One Lac Fifty Thousand Five Hundred

View 14 Replies View Related

Deleting Page Breaks From Text File Formatted

May 30, 2006

I am trying to figure out how to create a Macro code in Excel that will delete text file page breaks. Each page break starts with a square in column A and ends with the word continued in column D. I've tried several times, but when I test the code and I highlight the section to delete, it only deletes those specific rows next time. How can I get it to delete the rows from the square to "Continued" throughout the document without making reference to specific cell numbers?

View 3 Replies View Related

Changing Cell Comment Default Appearance

May 11, 2007

I wish to be able to change the default appearance of a cell's comment. Currently, the default font size is too small to be useful - is there a way of changing this setting, without reverting to a VBA tidy-up?

View 2 Replies View Related

Change Command Button Font Appearance

Dec 27, 2007

I am looking for a way to alter the appearance of a button when a second one is clicked. I have two buttons, "Yes" and "No", and when one is clicked I would like to 'grey' the font in the other without disabling it, and vice versa. I have tried recording a macro as I go into the button's properties and change the foreground font, but it does not record the font change, only the selection of the button object.

View 2 Replies View Related

Keeping Ending Zeros In Cells Formatted For Text Or General?

Nov 14, 2012

Is it possible to keep ending zeros in cells that are formatted for Text or General?

I have a column with numbers like the following: 264400

I need to format this number for three decimals so it will look like the following: 264.400

I need the column to be formatted for either Text or General. Currency breaks a system.

View 1 Replies View Related

Join Date And Time String And Keep Cell Appearance Same

Aug 23, 2007

I am joining two cells together, date and time.
I1 & J1
12/13/2005 12:35:00 PM

I want it to appear as
12/13/2005 12:35:00 PM

but after joining i get the serial value of:
38699 0.524305555554747

View 9 Replies View Related

Excel 2010 :: How To Make Concatenate To Generate A Text String Using Custom Formatted Value Of A Cell

May 2, 2014

I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).

number_forma.png

I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.

In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";

custom_format.png

As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.

Here is a screenshot of what the original and custom formatted cells look like:

custom_formatted_cells.png

As you can see the custom formatting works and does exactly what I wan

The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).

To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:

CONCATENATE("SOME STRING ", B1)

Where B1 is the custom formatted cell.

The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:

concatenate_formula.png

As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".

I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)

My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?

*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.

View 8 Replies View Related

Exponential Format

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

Exponential Value For Larger Numbers?

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

Calculate Exponential Forecast

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

Excel 2010 :: Macro To Change Excel Formatted Table Name Dependent On Number Of Loops

Jul 9, 2012

Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).

I am using excel for windows 2010. The macro that i have so far is below.

Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value

[Code] ........

View 1 Replies View Related

Excel 2007 :: Exponential Interpolation

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

Exponential Interpolation For Different Types Of Curves?

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

Changing Default Exponential Format

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

Avoiding Exponential Numbers During Import

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

ADVERTISEMENT