1. ## 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

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

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

5. You need a concreate example?

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

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

00\ 000\ 0000

8. 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
•