[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel change format in cell

Posted on 2011-05-08
2
Medium Priority
?
223 Views
Last Modified: 2012-05-11
I have created a form where users enter a part numer like 12345678. The visable output (same cell) should be 1234-56-78. This I have accomplisked via excel format cells and mask ####-##-##.

The problem now is that the part number can be longer like 1234567890 and should be displayed as 1234-56-7890.

How do I accomplish this?

Thank you!
0
Comment
Question by:Hydroscand
2 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35717977
Hi Hydroscand,

Would this mean that a number is either 8 char long and then it has 2 char to the right or 10 char and tren it has 4 char to the right?

you could try this:
it will change format acording to lenghts of entered field
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim myString As String
 Dim cel As Range, TARG As Range
 Dim KOLOM As Integer
Application.EnableEvents = False
On Error Resume Next
Set TARG = Intersect(Target, Range("A:A"))     'Obviously you can change this target range to your desired range
KOLOM = TARG.Column
 If TARG Is Nothing Then
   Application.EnableEvents = True
   Exit Sub
   Else
   myString = TARG
End If
If Len(myString) < 9 Then
Target.NumberFormat = "####-##-##"
Else
Target.NumberFormat = "####-##-####"
End If
End Sub

Kind regards
0
 
LVL 8

Accepted Solution

by:
wchh earned 200 total points
ID: 35718083
Another Option: Use conditional Formating:
Formula    =LEN(A1)<=8    Format ####-##-##
Formula    =LEN(A1)=9      Format ####-##-###
Formula    =LEN(A1)=10    Format ####-##-####

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question