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

You try:

Code:

---

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)

maybe try this

Code:

---

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.

I have tried this:

Code:

---

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

You need a concreate example?

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.

you can create a Custom Format for you Cells:

00\ 000\ 0000

oh, perfect - its works ... thank you