Custom Cell Format - Number Ending With Text String
Jun 13, 2013
How to modify a custom cell format to make data entry easier.
My goal is to have the output look like this: 'A12B-C20D: SET 10: Text string here'
Inputs needed are:
1-3 digit number between A & B
1-3 digit number between C & D
1-2 digit number after SET
Text string of variable length
Where I am stuck. So far I can get "A12B-C20D: SET 10:" to show up properly when 122010 is input. The custom format I am using looks like this: "A"##"B-C"##"D: SET "##": "
When I try to incorporate the "@" symbol the first part of the formatting goes away and the output shows up as "122010 Text string here".
I have tried:
"A"###"B-C"###"D: SET "##":";;-;_@
"A"###"B-C"###"D: SET "##": ";;@
"A"###"B-C"###"D: SET "##":";_@
"A"###"B-C"###"D: SET "##": ";;;@
View 4 Replies
ADVERTISEMENT
Oct 5, 2007
Data is: A1B2C3. I need:A1-B2-C3. Custom format 00-00-00 only works on numeric data?
View 8 Replies
View Related
Sep 14, 2007
I have been playing with custom formats recently and I'm wondering if there is anyway to make them wrap?
specifically I have several results that I have been formatting to show what they are related to
397235 visitors 392648 visitors 365487 visitors
I want these to display as visitors, but still be numbers so that they can be summed etc.
The problem is that I need the text to wrap so that visitors is on the second line in the cell. unfortunatley all i get is a ########## error to show the cell is not wide enough even with wrap text turned on.
View 9 Replies
View Related
Feb 22, 2013
where i can grab a number from a cell it would ALWAYS be the last string item..
for example if cell A1 has the following text:
04/02 Card Purchase 03/31 Google*1873060297 Cc@Google.Com CA Card 5036 $500.00
i want cell B1 to hold just the $500
this wold always be the case for everything im trying to do
View 5 Replies
View Related
Nov 29, 2009
I would like to format a row of cells. I want each cell in the row to contain (and show) both an interger and text. The interger will range in value from one to ~10,000. The text will always be two characters long...but these characters will change from cell to cell.
Elsewhere in the spreadsheet I will have a cell (w/ a formula) that counts the values of the intergers in the row, but ignores the text in each cell.
a procedure for format ing the cells in the row?
View 9 Replies
View Related
Aug 22, 2006
This is my formula and it returns: -55 bps.
=[ASK.xls]QTR!$AT$81&" "&"bps"
However, I want it to return (55 bps). I don't want to just add the parenthesis because in the future this formula could return a positive number. I tried to format the cell differently, but that did not work.
View 7 Replies
View Related
Jan 19, 2006
how can you custom a cell format in which you may in type in information that is displayed as ###-##-####-@# I searched everywhere and cannot find this.
For example, a medicare number.
I would need to type in 581566223b6, and it should be displayed as 581-56-2236-B6.
View 9 Replies
View Related
May 31, 2009
is it possible to custom format a cell in order to:
a) Present 3.33333 as 3.33 (max. of 2 decimal digits or less)
b) Present 3.333 as 3.33
c) Present 3.33 as 3.33
d) Present 3.3 as 3.3
e) Present 3 as 3 (NO zeros and NO decimal dot)
View 5 Replies
View Related
Aug 21, 2013
Are there any custom formats i could use to center align a particular text in a cell if it is chosen in a dropdown validation (example: N/A)?
I would like "N/A" to be aligned in the center and all other choices aligned to the left.
View 1 Replies
View Related
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
Aug 22, 2013
Is it possible to change cell format from custom to general or text and keep the cell value
I have this in formula bar: 16/08/2013 4:37:00 AM
which is displayed in the cell as: 16/08/2013 4:37
I want to change the cell format to general but display the same information.
View 2 Replies
View Related
May 13, 2013
I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
View 5 Replies
View Related
Mar 4, 2007
I have a cell that contains numbers and text.
example: 1-2-3-4-5-6 or 1-22-3-44-5-6.
If the the number between the dash '-' is a single digit (less than 9), I want to insert a leading zero so all 6 numbers are 2 digits.
Is this possible with custom formatting? I tried a custom for of:
00"-"00"-"00"-"00"-"00"-"00
and it is not working.
I also tried using a MID formula but it gets quite complex identifying the location of the dash - is there an easier way than the MID or LEN?
View 9 Replies
View Related
May 11, 2014
i am looking for excel custom number format for 0 (zero) number that make center alignment..
for example ;
sample (when type 0 (zero) number)
after custom number format
- (right alignment)
- (center alignment)
how make center alignment with custom number format for 0 (zero) number..
View 4 Replies
View Related
Apr 22, 2009
If I have the following info:
A1 = 5
A2 = -4
A3 = 0
How can I use Format Cells > Custom to have them display as:
A1 = 5 up
A2 = 4 dn
A3 = Level
I've been trying and trying but keep getting a message that I should use one of Excel's pre-defined formats.
View 3 Replies
View Related
Mar 25, 2014
Looking for a custom number format which will show numbers as follows:
0 = -
1234.89 = 1,235
-1234.43 = (1,234)
View 5 Replies
View Related
Jan 16, 2008
This workbook has 23 sheets for which this format will apply.
Under 1 man-hour .###
Over 1 man-hour #.##
Over 10 man-hour ##.#
Over 100 man-hour ###
Over 1000 man-hour #,###
View 13 Replies
View Related
Mar 6, 2009
I am trying to create a number format for formatting resistor value in their most commonly displayed form. For example 1,200 ohms would be displayed as 1.2kÙ and 5,000,000 ohms would be displayed as 5MÙ, and finally 5 ohms would be displayed as 5Ù. I have been able to create a format that formats any 2 contiguous formats but not all three.
for example: [>999999]###.0,,ÌÙ;[<1000000]###.0,kÙ;
or: [>999]###.0,kÙ;[<1000]###Ù
both work.(other similar criteria work as well) But if I try to put 3 conditions describing the desired formatting I get and error (containing no useful info) as I try to close the formatting dialog. Is there a limit to the number of conditions that can be used. Or something else I'm overlooking.
View 4 Replies
View Related
Feb 19, 2014
Format custom number setting for this below ; with condition, when I type following ;
1------001/2014 (showing data)
8------008/2014 (showing data)
45-----045/2014 (showing data)
17-----017/2014 (showing data)
364----364/2014 (showing data)
etc....
View 3 Replies
View Related
Dec 19, 2011
I know this works because I have seen it in a workbook at a client's office.
I need a custon number format to substiture an "em" dash for zero. An em dash is a hyphen with the width a the letter m. There is also the so called en dash tht is the width the the letter n. A search of the internet shows that to create an em dash in a cell that contains a 0 ("zero"), hold down the alt key and type 0151 on the numeric keypad. Typing 0150 insert a en dash.
I want to be able to set this up as a custom number format in the custom number dialog.
View 5 Replies
View Related
Feb 28, 2012
Instead of using conditional format, I would like to use a custom format to hide the number 1. Is this possible?
Basically only three things can be in this cell =IF(D8"",IF(D8="CIV",1.077,1),"")
I need to mask the 1, otherwise show the 1.077. The reason I am not using conditional formatting is because there is a condition on the cell already which applies a pattern style and a font will not match up to the pattern.
View 4 Replies
View Related
Jul 8, 2014
I am trying to create a custom number format.
Here is what I have come up with: 0.00%;[Red]-0.00%
The problem is, it has no "," it would display as 23555555.6% or -2355555.6%
I need it to display as: 23,555,555.6% or -23,555,555.6%
How would this be written?
Additionally, I have occasion to "abbreviate" in the following format:
23.55MM% or 1.5M% or -23.55MM% or -1.5M%
For currency, I have it written in this manner:
Currency: [>=1000000] $#,##0.0,," MM";[>0] $#,##0.0," M";[Red]($#,###," M")
I just freeze when trying to convert to percentage.
View 1 Replies
View Related
Mar 3, 2009
I am looking for a custom number format for phone numbers that will allow extensions to be typed in. I am trued using the standard phone format [
View 12 Replies
View Related
Nov 2, 2006
I am trying to work out a custom number format to display betting fractions properly - namely 3/2 as 6/4 and 2/3 as 4/6. The following works for 3/2:
[=1.5]"6/4 " ;??/??
However I can't get anything to work for 2/3 as it is a recurring decimal.
View 3 Replies
View Related
Apr 26, 2007
Is there any way to get a custom number format to display a multiple of the cell value? e.g. can I have a cell have the value 0.005 and display the value 50?
View 6 Replies
View Related
Dec 4, 2008
I have this data 0123-4567-8 but in the formula bar it only shows as 12345678. When I looked at the format it has as Custom 0000-0000-0 and it would be correct. The formula that I used to extract the numbers without the dash is =LEFT(A3,4)&MID(A3,6,4)&RIGHT(A3,1) the result is 12345678 So when I reference this number to 012345678 I get the wrong answer.
View 3 Replies
View Related
Jan 7, 2009
I have a column in my sheet which I need to be displayed with dashes.
Excample: 123456 -> 12-235-6 (just an example)
But once in a while in the code is a letter like: 123A56 which obviously I need to be displayed like 12-3A5-6
But whatever I do, nothing seems to work.
Another cell with Left()-Mid()-Right() is no option for me....
View 10 Replies
View Related
Aug 6, 2009
I have a number in a cell of 1025132.36. I need to scale it down to this number with this formatting: 1,025.13 (Two decimal places with comma separaters)
Basically it is dividing it by 1000 or scaling it by 3 (move the decimal place to the left three places). I cannot use /1000 in the cell and I cannot reference it to/from another cell. I must use something under Format/Cell. I was trying to create a custom format.
I saw in another forum thread to use #”.”###. This does the scaling but then I can’t make the number viewed with only two decimal places. Also when I tried to add comma separators for thousands it put a comma right before the decimal.
View 4 Replies
View Related
Apr 24, 2014
i am looking macro code to fixing value/number from custom number format (with dot mark criteria) as my data and will be as displayed fixing (what you see if what you get)....
2004.09.000907 (custom format) --------- 2004.09.000907 (after using macro)
1998.08.000001 (custom format)----------1998.08.000001 (after using macro)
if you press F2 in the sample number will be not visible dot (.) mark..
how to make/do that the number look like using custom number but fixed (remove custom number format)..
it's possible using macro?
View 2 Replies
View Related
Jun 4, 2013
We have a macro that formats a report from our accounting program quite extensively (mostly moving text around). The last step is to select the entire worksheet and set the cell format to a custom setting #,##0.00_);(#,##0.00). We haven't been able to include this in the macro - we end up manually doing this step after the macro has completed. Is there any way to set this custom format as part of the macro?
View 2 Replies
View Related
