PDA

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

Lexi9731
10-06-2022, 12:49 AM
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
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

Example:

=MyFormat(D2)

Aussiebear
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.

Lexi9731
10-06-2022, 04:59 AM
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
10-06-2022, 05:42 AM
You need a concreate example?

Lexi9731
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.

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

00\ 000\ 0000

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