☰

Excel でカスタム形式を設定して、18 桁のエントリにピリオド (区切り文字として) を配置するにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel worksheet-function format
Excel でカスタム形式を設定して、18 桁のエントリにピリオド (区切り文字として) を配置するにはどうすればよいですか?

これは、区切り文字として複数のピリオドを含む 18 桁の会計コードに関するものです。ピリオドを入力せずに数字だけを入力したいと思います。例:

112233333444555556

自動的に次のようにフォーマットされます

11.22.33333.444.55555.6

私は次の式を使用しました:

??"."??"."?????"."???"."?????"."?

最後の桁が常に0になる点を除いては機能します

最後のスペースに入力された任意の数字を受け入れるには、このコードをどのように変更すればよいでしょうか?

答え1

これは、列 A を監視し、テキスト入力が 18 文字の場合にドットを追加する VBA コードです。列全体を選択し、形式をテキストにすると、18 桁のコードを入力するか貼り付けることができます。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    If Len(Target) = 18 Then
        s = Left(Target, 2) & "." & _
        Mid(Target, 3, 2) & "." & _
        Mid(Target, 5, 5) & "." & _
        Mid(Target, 10, 3) & "." & _
        Mid(Target, 13, 5) & "." & _
        Right(Target, 1)
        Target.Value = s
    End If
Application.EnableEvents = True
End If

End Sub

答え2

@が述べたようにテイリンExcel は浮動小数点数の IEEE 754 仕様に準拠しており、15 桁の有効桁しか保存できません。最も簡単なマクロを使用しない回避策は、数式を 2 つの部分 (左端の 15 桁と右端の残りの桁) に分割することです。

=TEXT(
   LEFT([input], 15), format_left
 ) & 
 TEXT(
   RIGHT([input], LEN([input]) - 15), format_right
 )

つまり

= TEXT(LEFT(A2,15),"00-00-00000-000-000") & TEXT(RIGHT(A2,3),"00-0")

| Account number     | Account number (formatted) |
| ------------------ | -------------------------- |
| 112233333444555556 | 11-22-33333-444-55555-6    |

関連情報