format text in excel

[Guest]

How do i format a text cell in Excel, for example: 1122AA to 11-22-AA

 

[Bill Ridgeway]

Assuming that the text 1122AA is in cell A1 -

=LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&RIGHT(A1,2)

Regards.

Bill Ridgeway
Computer Solutions

 

[Mangesh Yadav]

if the length is fixed, and your text is in cell A1, then use:
=LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&RIGHT(A1,2)

Mangesh

 

[Anne Troy]

Hi, J. If you want this to occur automatically, and supposing you type these
values into column A, then you can right-click the worksheet tab and hit
View Code. Then paste this code into the code window that appears at right:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
Target(1).Value = Left(Target(1), 2) & "-" & Mid(Target(1), 3, 2) & "-"
& Right(Target(1), 2)

End If
Application.EnableEvents = True
End Sub

Code stolen from Chip Pearson's site and adapted for your purpose.
http://www.cpearson.com/excel/case.htm

*******************
~Anne Troy

www.OfficeArticles.com