Hyphen to cell vba

[Snabelhund]

Hi im Trying to write a simple part in a bigger macro that will convert a set of numbers and add a hyphen between number 8 and 9 see below for examples

The original numbers are fomatted as 000000000000
What i want to achieve i a format that looks like this: 000000-0000

I have a solution that works partly, but since some of the orignal numbers in the data set are formatted/contains both numbers and text it does seem to skip adding hyphen to theese cells.

As an example the original cell formatting can also look like 00000000AA00 instead of 000000000000 as described before . The outcome i´m looking for i that all cells are formatted as 000000-0000 or 000000-AA00 which im having trouble to achieve with my code.

Ill post the part that does the formatting below, (as mentioned it is part of a bigger module/ process)

Sub Workbook_Open()
    Dim rngh As Range, cellh As Range
    Dim myString As String
    Dim Cell As Range
    Dim wb As Workbook
    Set rngh = Range("B2:B5000")
    For Each cellh In rngh
        If Not IsEmpty(cellh) Then
            myString = cellh.Text
            myString = Right(myString, Len(myString) - 2)
            cellh.Value = myString
        End If
    Next cellh
    On Error GoTo NoFilledCells
  For Each Cell In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    Cell.Value = Format(Replace(Cell.Value, "-", ""), "000000-0000")
  Next
NoFilledCells:

Cheers and sorry for any spelling errors (from a non english speaking country)

 

[johnnyL]

If you just want to insert a dash ('-') after the sixth character from the left then how about:

replace:

    Cell.Value = Format(Replace(Cell.Value, "-", ""), "000000-0000")

with:

    Cell.Value = Left(Cell.Value, 6) & "-" & Mid(Cell.Value, 7, Len(Cell.Value) - 1)

 

[GWteB]

Hi, I sense some inconsistency in your explanation...

add a hyphen between number 8 and 9 see below for examples

In all your examples the hyphen apears to be at the 7th position (which is between 6 and 7).

The original numbers are fomatted as 000000000000
What i want to achieve i a format that looks like this: 000000-0000

This suggests that the last two characters may be dropped. Is that right?

 

[johnnyL]

@GWteB there are a few inconsistencies in the OP, but to answer your question about dropping characters, code in the OP removes the first two characters, If I read it correctly.

 

[Snabelhund]

You´re right apoligies for the inconsistency..
@ johnnyL Exactly the two first characters should be dropped and the dash should be added between position 6 and . Ill try solution provided and report back.

Again sorry for the somewhat inconsistent question and thanks for the replies

 

[Snabelhund]

The provided solution works fine, Thanks again

 

[johnnyL]

Glad to help @Snabelhund.