☰

Как настроить пользовательский формат в Excel, чтобы расставить точки (в качестве разделителей) в 18-значной записи?

tag-icon • tag-icon microsoft-excel worksheet-function format
Как настроить пользовательский формат в Excel, чтобы расставить точки (в качестве разделителей) в 18-значной записи?

Это касается 18-значных кодов учета, которые содержат несколько точек в качестве разделителей. Я хотел бы ввести только цифры, не вводя точки. Пример:

112233333444555556

автоматически отформатируется как

11.22.33333.444.55555.6

Я использовал эту формулу:

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

который работает, за исключением того, что последняя цифра всегда равна 0

Как следует изменить этот код, чтобы он принимал любую цифру, введенную в последнее поле?

решение1

Вот код VBA, который отслеживает столбец A и добавляет точки, когда текстовая запись содержит 18 символов. Выберите весь столбец и отформатируйте его как Текст, затем вы можете ввести или вставить 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 значащих цифр. Самый простой обходной путь без макросов — разбить формулу на две части (самые левые 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    |

Связанный контент