formatting cells #2

need to formatt cell to reflect L##.# (Eg K32.4)
Tried formatt cells custom and special. Any ideas would be 
appreciated.

Tks,

TM
0
anonymous (74722)
2/22/2004 1:38:08 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
395 Views

Similar Articles

[PageSpeed] 17

Number format works with numbers--not text.

You'll either need to type it in the way you want or maybe use a macro that
inserts leading zeros(???) after the letter.

But I think you'll have to post some more details to get a better response.

tmarta wrote:
> 
> need to formatt cell to reflect L##.# (Eg K32.4)
> Tried formatt cells custom and special. Any ideas would be
> appreciated.
> 
> Tks,
> 
> TM

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/22/2004 4:23:53 PM
Try this Custom format
"L"#,##
Change the decimal places to suit your needs.

t


0
anonymous (74722)
2/25/2004 12:11:09 AM
I need the L in the format to reflect any letter of the 
alphabet that is typed in. Then to show 2 numbers a 
decimal point then another number or two.
>-----Original Message-----
>tmarta,
>Did you want an L or a K, or am I misinterpreting your 
question?  A custom format of L##.# will display L32.4 if 
you enter 32.4 in the cell.
>
>Good Luck,
>Mark Graesser
>mark_graesser@yahoo.com
>Boston MA
>     
>     ----- tmarta wrote: -----
>     
>     need to formatt cell to reflect L##.# (Eg K32.4)
>     Tried formatt cells custom and special. Any ideas 
would be 
>     appreciated.
>     
>     Tks,
>     
>     TM
>     
>.
>
0
anonymous (74722)
2/25/2004 4:15:40 PM
Thanks TJ,
I need the "L" in the formula to reflect any letter of the 
alphabet that might be needed. Is there a formula to 
reflect that?
>-----Original Message-----
>Try this Custom format:
>"L"#,##0
>Change the decimal places to suit your needs. 
>
>tj
>
>
>.
>
0
anonymous (74722)
2/25/2004 4:19:08 PM
Thank you for your response!!  Can you format so that all 
you do is type in the info into the cell and it will show 
formatted the way you need it to.  Eg. when you type in 
8987 you can formatt the cell to reflect 89 87 with a 
space. Can you formatt to reflect a decimal place with any 
letter to begin with? So if I type P569 it willl be 
reflected in the cell to show P56.9?(the letter in front 
is always different)
>-----Original Message-----
>tmarta,
>Did you want an L or a K, or am I misinterpreting your 
question?  A custom format of L##.# will display L32.4 if 
you enter 32.4 in the cell.
>
>Good Luck,
>Mark Graesser
>mark_graesser@yahoo.com
>Boston MA
>     
>     ----- tmarta wrote: -----
>     
>     need to formatt cell to reflect L##.# (Eg K32.4)
>     Tried formatt cells custom and special. Any ideas 
would be 
>     appreciated.
>     
>     Tks,
>     
>     TM
>     
>.
>
0
anonymous (74722)
2/25/2004 4:33:06 PM
If you wanted to use a formula in a helper column, you could do something like:

=left(a1,3)&"."&right(a1,1)
maybe better to check to see if something "nice" was entered:
=IF(LEN(A1)<>4,A1,LEFT(A1,3)&"."&RIGHT(A1,1))

If you want to "format" the same cell, you'll have to use a macro:

Right click on the worksheet that needs this stuff.
select view code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
    If Len(Target.Value) <> 4 Then Exit Sub
    
    On Error GoTo errHandler:
    With Target
        Application.EnableEvents = False
        .Value = UCase(Left(.Value, 3)) & "." & Right(.Value, 1)
    End With
    
errHandler:
    Application.EnableEvents = True
    
End Sub

I used column A, so adjust that range as required.

A couple of links that may help:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
or
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

anonymous@discussions.microsoft.com wrote:
> 
> Thanks TJ,
> I need the "L" in the formula to reflect any letter of the
> alphabet that might be needed. Is there a formula to
> reflect that?
> >-----Original Message-----
> >Try this Custom format:
> >"L"#,##0
> >Change the decimal places to suit your needs.
> >
> >tj
> >
> >
> >.
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/26/2004 3:20:24 AM
Reply:

Similar Artilces:

2 Locations (exchange mode)
SBS2k3, Exchange 2k3, Outook2k3 I am in HQ, using exchange2003, only SMTP service open, POP3 stop. I'd like branch office accessing Exchange 2003 by exchange mode also, not POP3. Can I setup a VPN line and run the above module over the VPN line? Thanks for answering Yes you can. Another option would be to use RPC over HTTP(s) this would mean you do not need a VPN connection. Nue "Smith" <Smith@Micro> wrote in message news:%23vKHe$1mGHA.2256@TK2MSFTNGP04.phx.gbl... > SBS2k3, Exchange 2k3, Outook2k3 > > I am in HQ, using exchange2003, only SMTP service open, ...

conditional formatting #11
Can you please tell me how to do conditonal formatting example NUMBER IN A CELL(B2) is 20 show in cell (b6) Criteria - 1 is less than in cell(b3) - shown in red &if greater than In cell (b4) - shown in blue & if greater than cell(b5) - shown in green How can I then do the above for a other rows(ie. copy this conditional format) Appreciate your help Please ASAP VGupta ...

Conditional Formatting with XML
I recently became aware of the possibility of using conditional formatting to highlight the cells in a spreadsheet which contain formulas. This uses the GET.CELL method from XML. While it cannot reference another workbook (like Personal.xls), I have still been able to write a macro in my Personal.xls folder that sets the name range and conditional references in the active workbook; while I "wrote" it, I heavily borrowed from others posts on a website :) The function and sub below toggle this conditional formatting for the entire worksheet. Question: how do I set this up so that ...

Conditional formating #2
Hi, I've worked out how to do conditional formating when "x<y go red" but how do you get "if x<y then display y" We are having a problem with stats going over 100% so what I want is that if it calulates over 100% then to only display 100% and not the correct figure. Thanks Stephen Stephen I assume you have a row of numbers, say B1:J1, that should total 100% when aggregated but you are encountering rounding differences when the total is calculated in, say, cell K1 If its just a presentation issue, then select the Column K, select "Format>Cells>N...

Sell cvv + dumps + track1/2 + bank login + do transfer...all country !
Hello, i'm seller and i need buyer vip for work together My age 25 From Viet Nam YH: need_buyer_vip Email: need_buyer_vip******.com *** i'm seller ccv..dumps..track 1/2..acc paypal..do transfer..ship..all country*** ----------- List cc i have (100% Good and cheap ) ---------- - COuntry : US + UK + AU + CA + EU ( fr, ger, ita , ireland, spain...more....) + .....more...( contact me to know more ).......... -Type : visa / master / amex / disco / with bin / with dob / with ssn / fullz / - If you buy bulk i will discount for you and i will change if c...

550 5.7.1 Unable to relay for #2
Hello, We have a customer running Exchange 2003 on win 2003 server. windows domain name ATG.internal. We have 4 different Email domains registered in internet DNS all users have an email address @abilityemployment.com.au some users also have @nwes.com.au some users also have @northwestcontractors.com.au some users also have @abilitylifeskills.com.au This used to work fine and people received their mail which ever of their addresses it was sent to. now Suddenly only @abilityemployment.com.au mail is received and all mail to the other domains is rejected, and the sender receives a message...

table based i one field splitted in 2 fields
Hi everyone, I have a txt file with product EAN and label quantity to print. 3564700010822 2 3564700010983 5 3564700422687 10 I have a form to print label quantity. What i pretend is "convert" the table in a 2 fields table or query like: ProductCode |Quantity 3564700010822|2 3564700010983|5 3564700422687|10 Any help is apreciated. -- Thanks, Sorry if my english isn''t correct, but I''m from Potugal ;) Emanuel Violante Galeano On Thu, 29 Apr 2010 07:27:01 -0700, Emanuel Violante <EmanuelViolante@discussions.microsoft.com>...

Nested Ifs In Conditonal Formats
Is it possible to have nested conditions using Conditional Formatting? I have a range of columns that change colour according to thei totals. Here is the Psuedo code. If E67 = 4 Then E10 Font = GREEN BOLD, F10 = RED BOLD Else E10= WHITE(invisible), E11 = WHITE(invisible), If F67 = 7 Then E10 Font = WhiteF10 = GREEN BOLD, G11 = RE BOLD........ and so on....... Basically the first Column Header Font goes From white to Green , th next column header font goes red, then when that one changes green th next one goes red etc etc. If this makes sense to anyone, can it be done -- ANDYPAN ----------...

Is there an Excel format foro time to hundreths of a second
I am trying to record times for swimming in the format of minute, seconds, hundreths of a second mm:ss.hhh -- is there a format for this available? I also need to do functions - add, subtract, max, min, etc. Is there an easy way to do this without writing macros? Give the cell a custom format: Format|cells|Number format|custom category mm:ss.000 or [mm]:ss.000 if you need to sum them. But enter your values as real times (include the hour, minute, second, and hundredths. 00:12:05.001 (for 12 minutes, 5 seconds plus a little bit.) teetime wrote: > > I am trying to record times ...

2-Column Layout with auto text flow?
I would like to have a worksheet that has a 2-column "newspaper" format, where text flows off the bottom of the first column to the top of the second column. Is this possible using Excel2000? As a very last resort, I'll use a macro to split the text and put it into different cells. But this is messy and not very accurate. My wish is to somehow link cells together and have the text flow between them, or use a control which will do this. Thanks! -- Matt Kruse http://www.JavascriptToolbox.com why dont you use Word? "Matt Kruse" <newsgroups@mattkruse.com> wr...

macro to copy Vlookup formula to some cells with a filter on
Hello, I was wondering if somebody could help me with this macro… I have some data with a filter on, so that only the rows with blank cells are showing. I want to enter a VLOOKUP in the cell, and copy it down to all the blank cells. I tried just recording a macro, but when I run the macro it doesn’t work. Any ideas of how to do it? Example: 1) Before the filter: Colums A -B - C A x x - 1st Row A - 2nd Row A x x - 3rd Row A - 4th row A x x - 5th row 2) After the fil...

CRM 1.2 Licensing
hi all, i got MSDN Universal subscripton. Now i'm having MSCRM 1.0. which i wantt to upgrade to 1.2. my problem is that how should i get the license for the 1.2 . In the website i could see link for partrners only how should i get the licencing done for CRM 1.2 Thanks in advance Jack Jack If you have bought crm then you can request a license from the company who sold you crm. Can you tell me whether you are running a full crm license or an msdn one? "Jack" <jack@somewhere.com> wrote in message news:e1M3dYG0DHA.536@tk2msftngp13.phx.gbl... > hi all, > i go...

Combo boxes #2
How do I get auto-completion into my combo box?? Is this just a setting for the comob or do I have to code it. If I have to code it, could some one guide me on the functions to over-load? TIA "Golum256" <Golum256@discussions.microsoft.com> wrote in message news:AD09B86D-C9C0-491B-A342-47B158EB23F3@microsoft.com... > How do I get auto-completion into my combo box?? > > Is this just a setting for the comob or do I have to code it. > If I have to code it, could some one guide me on the functions to over-load? http://msdn.microsoft.com/library/default.asp?url=/libra...

Synchronising Money 2005 between 2 PCs
Hi, I know that Money uses ActiveSync to sync Money between a handheld and a PC. My question is, Is there such a thing as synching between 2 PCs? I would like to sync the money on my laptop and my desktop. I want to use it this way since i only have my laptop when i am on the move and my partner at home uses the desktop to track our expenses. I would like to keep all our expenses centralized to one file. ...

FORMATTING A 19 DIGIT NUMBER
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3FAAD.1459EDF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I need some help to format a 19 digit budget code number. The format = must be in this format: "XXXX-XXXX-XX-XXX-XXXXXX" (without the quotes). An example of a budget code number that we use is: = 1110-2110-38-068-611521 Thank you. Wayne ------=_NextPart_000_0008_01C3FAAD.1459EDF0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTY...

Two-digit Hours Format specifier "hh" doesn't work
Access help states that the Format property of a control on a form or report can be used to format the hours part of a time into two digits by specifying "hh", e.g., "dd-mmm-yyyy hh:nn:ss" should yield, e.g., 12-Jan-2010 00:00:00. OK, seems simple enough. It works for the day. It works for the minutes and seconds. BUT IT DOESN'T WORK FOR THE HOURS. Instead what I get is "12-Jan-2010 0:00:00". In other words, the hours are displayed with one digit AND NOT TWO. This is driving me crazy. Why doesn't this just work? -- Randy Yates ...

Cell Validation for Decimals
How can I set the cell validation of a list of cells to have a value(less than 4 Decimal place)? Thanks. Ringo Tan "ringo" <ringo@discussions.microsoft.com> wrote > How can I set the cell validation of a list of cells to have a > value(less than 4 Decimal place)? (Adapted from a previous post by Ron Rosenfeld) Select the list of cells, say A1:A10 Click Data > Validation > Settings tab Select "Custom" under Allow: Put in the "Formula:" box: =(A1*10^3)=INT(A1*10^3) Click OK -- Rgds Max xl 97 --- Please respond in thread xdemechanik <...

Conditional formatting with time
I’m trying to set up a conditional format for an entire row. This sheet is used to track arrival times at a specific location. The location has a scheduled arrival time in column C, and in column D I enter the actual arrival time, and column E calculates the difference. I use the 1904 time system so I’ll be able to calculate and show negative time. Column E is set with =IF(D3=””,””,(D3-C3)) this will have column E appear blank until the actual time is entered. Without =IF(D3=””,””,(D3-C3)) in column E and with =D3-C3 this will show the negative of the scheduled time i.e. -4:59 o...

Group email #2
Hello, I would like to send the same email to a group I created. The only problem is everyone in the group gets everyone elses email addresses. How do you send without giving the whole group's email addresses? This is not for spam, just my wife's mailing list. Send it to yourself with a bcc to the group. -- Carlos "chas638" <chas638@discussions.microsoft.com> wrote in message news:4522D71E-934D-4C88-83E6-4CF8C1CC1E75@microsoft.com... > Hello, > I would like to send the same email to a group I created. The only problem > is everyone in the group gets ever...

Mailbox Size discrepancy #2
Hi, I have an issue with the size of a mailbox reported by outlook and exchange system manager. The environment is set up as such: Exchange 2003 SP1 Outlook 2003 SP1 Outlook in cached mode 100mb mailbox size limit The issue is that Outlook reports the mailbox as a size a lot less than reported in Exchange System manager eg 75mb in outlook but 125mb in exchange system manager. The user gets a warning about the mailbox size and starts deleting mail. They then check the size and it is less than the 100mb limit and yet they still keep getting the error message and are stopped from ...

V1.2
Hello all, I need to import customer from another data source into accounts in V1.2. In the import menu i see only the possibility to import leads or contacts, how can i do? there is a easy way to do this? Can I import my data into leads and bulk convert them into account? Thanks and regards. Diego There is no "bulk convert" function. You have a few choices. You can easily do this using Scribe or by writing a simple import process using the SDK. -- Matt Parks MVP - Microsoft CRM "Diego Pianarosa" <d.pianarosa@globsys.it> wrote in message news:eTPkyb5F...

Input string not in a correct format
hi, i have written the following code (which works fine, no errors in vs.net) using crm sdk 1.2. however, when the user (member of the admin group) opens ie using a sbs machine, he receives the error: "Input string was not in a correct format. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.FormatException: Input string was not in a correct format. Source Error: An unhandled exception was generated duri...

Exchange Migration #2
Hello everybody. I'm not a exchange expert so please try to help me on this one. We've migrated Exchange 5.5 to the 2003 version successfully. Exchange 5.5 was running on a NT (BDC) machine with the latest service pack and we're using Windows server 2003 for the new exchenge server. Everything is working fine until I disconnect the NT machine. I can receive emails from the outside, but not the same when i'm trying to send one. when i connect the the NT back, all the emails reached its destination!, whats is wrong with my servers?? any help will be appreciated. Thanks in adva...

Exchange Rule #2
I have well over 500 users on an Exchange 2003 environment. We have a spam filter in place and all spam that comes in is tagged with the word spam on the subject line. Is there a way to setup all clients so when they get an email that is tagged with spam, it would automatically be sent to the junk folder of Outlook? I know they can do it manually in Outlook but since we have over 500 users, this is not a feasible solution for us. Is there a way to configure Exchange to do this automatically? Why not just use IMF? "Ricky" <Ricky@discussions.microsoft.com> wrote in me...

Duplicate messages in INBOX #2
I have Outlook 2000 installed on an XP professional machine. As of three days ago it now has duplicated each message inbound. How do I stop this carnage filling up my inbox. Thank you Scotty Do you have your AV set to scan incoming and outgoing mail? IF yes, turn it off. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, udontknow asked: | I have Outlook 2000 inst...