formatting cells

[tmarta]

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]

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.

 

[tjtjjtjt]

Try this Custom format
"L"#,##
Change the decimal places to suit your needs.

t

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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.

 

[Dave Peterson]

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