View Full Version : [SOLVED:] Zahlen-Formatierungen kombinieren

10-06-2022, 12:49 AM

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

10-06-2022, 02:17 AM
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



10-06-2022, 02:33 AM
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.

10-06-2022, 04:59 AM
I have tried this:

Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VALUE(RC[-14])"
Selection.AutoFill Destination:=Range("P2:P5000"), Type:=xlFillDefault
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

10-06-2022, 05:42 AM
You need a concreate example?

10-07-2022, 02:54 AM
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.

10-07-2022, 05:23 AM
you can create a Custom Format for you Cells:

00\ 000\ 0000

10-07-2022, 10:31 AM
oh, perfect - its works ... thank you