I write Cobol format in VBA excel please help me with such as?

[Faozan]

Such as asdfghjk =>asd-fg-hijk (alphanumeric)
1234567 => 12-345-67 (numeric)

 

[jolivanes]

Change references where required.

Sub Here_We_Go()
Dim c As Range
    For Each c In Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row)
        If IsNumeric(c.Value) Then
            With Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .NumberFormat = "@"
                .Value = Left(c, 4) & "-" & Mid(c, 5, 3) & "-" & Mid(c, 8)
            End With
        Else
            With Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .Value = Left(c, 2) & "-" & Mid(c, 3, 2) & "-" & Mid(c, 5)
            End With
        End If
    Next c
End Sub

 

[Faozan]

Change references where required.

Sub Here_We_Go()
Dim c As Range
    For Each c In Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row)
        If IsNumeric(c.Value) Then
            With Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .NumberFormat = "@"
                .Value = Left(c, 4) & "-" & Mid(c, 5, 3) & "-" & Mid(c, 8)
            End With
        Else
            With Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .Value = Left(c, 2) & "-" & Mid(c, 3, 2) & "-" & Mid(c, 5)
            End With
        End If
    Next c
End Sub

Hi, How to create fix length and add currency numbers such as 123456789 to 6789.00 or 1234.00

 

[jolivanes]

And how should this end up to be?
12
123

 

[Faozan]

And how should this end up to be?
12
123

Yes

 

[jolivanes]

Yes what?
Should that be 12 or 12.00 or 1200.00
Or should it be $1200.00?

 

[Faozan]

12.00

 

[Faozan]

Yes what?
Should that be 12 or 12.00 or 1200.00
Or should it be $1200.00?

12.00

 

[jolivanes]

Do me a favor please. Don't quote unless it is absolutely required, which to my knowledge it very seldom is.
Refer to Post numbers and/or member's name.

 

[Faozan]

Do me a favor please. Don't quote unless it is absolutely required, which to my knowledge it very seldom is.
Refer to Post numbers and/or member's name

Member’s name

 

[jolivanes]

Sub Here_We_Go()
Dim c As Range
    For Each c In Sheets("Sheet1").Range("C2:C" & Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row)
        If IsNumeric(c.Value) Then
            With Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .NumberFormat = "@"
                .Value = Left(c, 4) & ".00" & "-" & Mid(c, 5, 3) & ".00" & "-" & Mid(c, 8) & ".00"
            End With
        Else
            With Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .Value = Left(c, 2) & "-" & Mid(c, 3, 2) & "-" & Mid(c, 5)
            End With
        End If
    Next c
End Sub

What I mean by Post #18 is to NOT use the quote button.