Conditional Question

[Legacy 5596]

Using the Conditional formating, I'd like to look at the first number in cell A2 and if that number begins with a 9 I'd like to format the cell as TEXT, otherwise leave it general. -- Can this be done?
(no VB please)

 

[Mark W.]

Conditional formatting doesn't govern number formatting.

 

[Legacy 5596]

Ok thanks. I didn't see anything like that in there, but I wanted to ask the gurus

 

[Mark W.]

Of course you could always define a formula to reformat your data in another column...

=IF(LEFT(A2)="9",TEXT(A2,"0"),A2)

 

[Mudface]

If you're willing to give VBA a shot, right-click on the sheet tab, select view code and copy and paste in the following: -
<pre>
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Left(Target, 1) = "9" Then
Target.NumberFormat = "@"
Else:
Target.NumberFormat = "General"
End If

End Sub
</pre>