Excel自定义格式检查字符 [英] Excel Custom format Check for character
问题描述
$ b $ ol <$>
。
zeroes添加到开头。
。
。
未格式化的数据
-534
1083
386840/2
12345/10
$ c $
$ b $ p $ <$ p
$ b 预期结果: c $ c> 000534
001083
38684002
01234510
我到目前为止:
000000; 000000; 0是非常明显的部分,没有/的值将被读作数字。如果它是一个数字,我会使用 [> 1000] 0
或类似的东西,但它不适用于 Text
就我理解。
公式我正在使用(只是现在),而不是所需的自定义格式:
$ b $ (a)(b)
$ $ $ $ $ $ $ $ $ $ $ $ $ $ ;
IF((LEN(A1)-FIND(/; A1))= 1;
SUBSTITUTE(CONCATENATE(REPT(0; 7-FIND(/; A1)) (); A1);/;0);
SUBSTITUTE(CONCATENATE(REPT(0; 7-FIND(/; A1)); A1);/;)) )
任何人都可以给我一个提示吗?
以下是使用 NumberFormat
完成所需操作的一种方法。适当的数字格式是由VBA事件代码生成的。
有一个很大的缺点,就是每个包含/
,并且允许的数字格式的数量可以是限制的。
然而,它不需要帮助列,不会改变存储在单元格中的原始数据。
在列A中输入的任何内容都将根据规则进行格式化。
如果存在斜线,代码将会改变数字部分;但不测试看到两者都是数字。所以 abc / 1
- > abc01
,但是如果你愿意的话,你可以改变它。
这是工作表代码:
Option Explicit
Private Sub Worksheet_Change(ByVal Target作为范围)
Dim FormatRange As Range,C As Range
Const Fmt As String =000000; 000000; 000000; @
Dim V As Variant
Set格式范围=范围(A:A)
如果不相交(目标,格式范围)是没有那么
在相交(目标,格式范围)中的每个C
如果InStr(C.Text, /)= 0 Then
C.NumberFormat = Fmt
else
V = Split(C.Text,/)
V(0)=格式(V 0),000000)
V(1)=格式(V(1),00)
C.NumberFormat =;;; &安培; Chr(34)&加入(V,)& Chr(34)
End If
Next C
End If
End Sub
What I need is custom format to change values according to these rules:
- If "/" is absent and number's length is less than 6, add zeroes to the begining.
- If "/" is present and number's length before"/" is less than 6, add zeroes to the begining.
- If "/" is present and number's length after "/" is 1, add zero before last character.
- If "/" is present and number's length after "/" is 2, leave as it is.
- Total length of string with "/" would be 8, without it - 6
Unformatted data:
-534
1083
386840/2
12345/10
Desired result:
000534
001083
38684002
01234510
What I came up so far:
000000;000000;0 is pretty obvious part, value without "/" will be read as number anyway. If it was a number, I would use [>1000]0
or something like that but it doesn't work with Text
as far as I understood.
Formula I'm using (just for now) instead of desirable custom format:
=IF(ISERROR(FIND("/";A1));
CONCATENATE(REPT("0";6-LEN(A1));A1);
IF((LEN(A1)-FIND("/";A1))=1;
SUBSTITUTE(CONCATENATE(REPT("0";7-FIND("/";A1));A1);"/";"0");
SUBSTITUTE(CONCATENATE(REPT("0";7-FIND("/";A1));A1);"/";"")))
Can anyone give me a hint?
Here is one way of accomplishing what you want using NumberFormat
. The appropriate number format is generated by VBA event code.
There is a BIG disadvantage in that you will need a different number format for every unique entry that contains a "/"
, and the number of allowable number formats can be limiting.
However, it does not require helper columns, and does not alter the original data stored in the cell.
Anything entered in column A will be formatted according to the rules.
If a slash is present, the code will alter the numeric sections; but does not test to see that both are numeric. So abc/1
--> abc01
, but you can change that if you like.
This is Worksheet code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FormatRange As Range, C As Range
Const Fmt As String = "000000;000000;000000;@"
Dim V As Variant
Set FormatRange = Range("A:A")
If Not Intersect(Target, FormatRange) Is Nothing Then
For Each C In Intersect(Target, FormatRange)
If InStr(C.Text, "/") = 0 Then
C.NumberFormat = Fmt
Else
V = Split(C.Text, "/")
V(0) = Format(V(0), "000000")
V(1) = Format(V(1), "00")
C.NumberFormat = ";;;" & Chr(34) & Join(V, "") & Chr(34)
End If
Next C
End If
End Sub
这篇关于Excel自定义格式检查字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!