Consulting

Results 1 to 8 of 8

Thread: Zahlen-Formatierungen kombinieren

  1. #1
    VBAX Regular
    Joined
    Oct 2022
    Posts
    12
    Location

    Zahlen-Formatierungen kombinieren

    Hello,

    I try to format 7 to 9-digit article numbers uniformly, e.g. 20 154 184 5 or 00 845 157 4.

    In column D I have the original data, which consists of 7 to 9-digit sequence of numbers that Excel recognises as text.

    Now I tried to convert this text into a number in column E with the formula =VALUE(D2) and then generate the uniform classification with user-defined formatting ## #### ###. This also works with 9-digit article numbers, with the 7-digit ones just 2 digits are missing.

    So next attempt with =REPEAT("0";9-LENGTH(D2))&D2 .... Now he fills up all article numbers in 9 digits, partly with 2 preceding 0. But he does not recognise the result as a number and thus the user-defined formatting does not work.

    So I need a combination of both, gladly also with VBA programming.

    Does anyone have an idea?

    Thank you very much for your help
    Last edited by Aussiebear; 10-07-2022 at 03:17 AM. Reason: Converted to English

  2. #2
    You try:
    Public Function MyFormat(ByVal v As Variant)
    MyFormat = v
    If IsNumeric(v) = False Then
        Exit Function
    End If
    MyFormat = Format$(v, "00\ 000\ 0000")
    End Function
    Example:

    =MyFormat(D2)

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,235
    Location
    maybe try this

     Sub ConvertTextToNumber()
    With Range("B5:B14") <---- change this to suit your range in column D
    .NumberFormat = "General"
    .Value = .Value
     End With
    End Sub
    If you run this before doing anything else, it should convert your range to numbers.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    VBAX Regular
    Joined
    Oct 2022
    Posts
    12
    Location
    I have tried this:

    Range("P2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=VALUE(RC[-14])"
        Range("P2").Select
        Selection.AutoFill Destination:=Range("P2:P5000"), Type:=xlFillDefault
        Range("P2:P5000").Select
        Selection.NumberFormat = "## ### ### #"
    And thus copied the original values from column B to column P, and made the formatting there ... but unfortunately without adding 0 to the missing digits
    Last edited by Aussiebear; 10-06-2022 at 08:15 PM. Reason: Added code tags to supplied code

  5. #5
    You need a concreate example?
    Attached Files Attached Files

  6. #6
    VBAX Regular
    Joined
    Oct 2022
    Posts
    12
    Location
    Thank you - the function is really good ... but I need this as an automatic function, i.e. within a sub ... or, what do I have to write in my sub for this function to be retrieved?

    It would also be nice that the column of the original data is overwritten in the new formatting.
    Last edited by Aussiebear; 10-07-2022 at 03:18 AM. Reason: Converted to English

  7. #7
    you can create a Custom Format for you Cells:

    00\ 000\ 0000

  8. #8
    VBAX Regular
    Joined
    Oct 2022
    Posts
    12
    Location
    oh, perfect - its works ... thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •