Zahlen-Formatierungen kombinieren

[Lexi9731]

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

[arnelgp]

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)

[Aussiebear]

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.

[Lexi9731]

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

[arnelgp]

You need a concreate example?

[Lexi9731]

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.

[arnelgp]

you can create a Custom Format for you Cells:

00\ 000\ 0000

[Lexi9731]

oh, perfect - its works ... thank you