Insert dashes within character string, within an array.

[pgiering]

ISO VBA command to insert a dash (hyphen) as the 5th character from the left, and 6th character from the right, in a string of varying length, within an array.

For example...
Column Z contains the following list of values:
MSEAVIMA0013S
MSEAVIRM0016S
MOLPVIRM0017S
MOLS130012S
MSEAVIRM0011S
MOL21300040S
MSEAVIMA0008S

I would like to run a macro which converted them to:
MSEA-VIMA-0013S
MSEA-VIRM-0016S
MOLP-VIRM-0017S
MOLS-13-0012S
MSEA-VIRM-0011S
MOL2-130-0040S
MSEA-VIMA-0008S

The rows may vary (e.g. - it might be 2-8, or it might be 3-49, etc.), so I would plan to select the range before running the macro.

Thank you!

Paul

 

[lrobbo314]

How about this?

Sub DASH()
Dim r As Range: Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant: AR = r.Value
Dim a As String

With Application.WorksheetFunction
    For i = LBound(AR) To UBound(AR)
        a = AR(i, 1)
        AR(i, 1) = .Replace(.Replace(a, 5, 0, "-"), Len(a) - 3, 0, "-")
    Next i
End With

r.Value = AR
End Sub

 

[lrobbo314]

And just for fun a Power Query variant.

Book1
	C	D	E
1	Column1		Column1
2	MSEAVIMA0013S		MSEA-VIMA-0013S
3	MSEAVIRM0016S		MSEA-VIRM-0016S
4	MOLPVIRM0017S		MOLP-VIRM-0017S
5	MOLS130012S		MOLS-13-0012S
6	MSEAVIRM0011S		MSEA-VIRM-0011S
7	MOL21300040S		MOL2-130-0040S
8	MSEAVIMA0008S		MSEA-VIMA-0008S
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Insert = Table.TransformColumns(Source,{{"Column1", each Text.Insert(Text.Insert(_,4,"-"),Text.Length(_)-4,"-")}})
in
    Insert

 

[pgiering]

How about this?

Sub DASH()
Dim r As Range: Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant: AR = r.Value
Dim a As String

With Application.WorksheetFunction
    For i = LBound(AR) To UBound(AR)
        a = AR(i, 1)
        AR(i, 1) = .Replace(.Replace(a, 5, 0, "-"), Len(a) - 3, 0, "-")
    Next i
End With

r.Value = AR
End Sub

Hi Irobbo314, thank you(!), this did exactly the function I want but not in the right place.
The code you provided is set for data in column A, starting at row 1.
The report I am working with has the data in column Z, so I could change the code from A to Z easily enough, but the range does not normally begin on row 1, it is always a variable and would always be preselected by me before launching the macro.
I don't know how to tell it only to work in the range I have selected.
Can you make that revision?

As background, this report is a list of payments received from a large customer (i.e. - a remittance advice), where the customer lists all the invoices they are paying, which may be hundreds at a time. The reason I need to enter dashes is that our invoice number has these dashes, but some of their processors do not enter them. Some do, but others don't, so this is part of cleaning up their data before trying to do an automated line-by-line match of what they provided to what we have in our system. This is why I can't do every line in the column at once. Some already have it.

Thanks again for your help!

 

[lrobbo314]

Like this?

Book1
	Z
10	MSEA-VIMA-0013S
11	MSEA-VIRM-0016S
12	MOLP-VIRM-0017S
13	MOLS-13-0012S
14	MSEA-VIRM-0011S
15	MOL2-130-0040S
16	MSEA-VIMA-0008S
Sheet4

Sub dash2()
Dim r As Range: Set r = Range(ActiveCell, ActiveCell.End(xlDown))
Dim AR() As Variant: AR = r.Value
Dim a As String

With Application.WorksheetFunction
    For i = LBound(AR) To UBound(AR)
        a = AR(i, 1)
        AR(i, 1) = .Replace(.Replace(a, 5, 0, "-"), Len(a) - 3, 0, "-")
    Next i
End With

r.Value = AR
End Sub

 

[pgiering]

That worked at the top (i.e. - it started with my active cell), but it did not stop at the bottom.
I think I did not provide a good example set.

For example... (REVISED by showing the column Header and row #'s, and by adding two lines at the bottom )
Column Z contains the following list of values:
1 Invoice Number
2 MSEAVIMA0013S
3 MSEAVIRM0016S
4 MOLPVIRM0017S
5 MOLS130012S
6 MSEAVIRM0011S
7 MOL21300040S
8 MSEAVIMA0008S
9 MSEA-VIRM-0047S
10 MSEA-VIMA-0043S

I would like to run a macro which converted them to:
1 Invoice Number
2 MSEA-VIMA-0013S
3 MSEA-VIRM-0016S
4 MOLP-VIRM-0017S
5 MOLS-13-0012S
6 MSEA-VIRM-0011S
7 MOL2-130-0040S
8 MSEA-VIMA-0008S
9 MSEA-VIRM-0047S
10 MSEA-VIMA-0043S

Because I only need to add dashes to rows 2-8, I would first select that range. With the macro as it currently is, everything works except it does not stop at row 8, it continues to the bottom of the column. As I work further through the report, I might run the macro again with a different range selected, and would again want it to stop after that specific selection had been adjusted.

Thanks!

 

[lrobbo314]

Try this one.

Sub dash2()
Dim r As Range: Set r = Range(ActiveCell, ActiveCell.End(xlDown))
Dim AR() As Variant: AR = r.Value
Dim a As String

With Application.WorksheetFunction
    For i = LBound(AR) To UBound(AR)
        a = AR(i, 1)
        If InStr(a, "-") = 0 Then AR(i, 1) = .Replace(.Replace(a, 5, 0, "-"), Len(a) - 3, 0, "-")
    Next i
End With

r.Value = AR
End Sub

 

[pgiering]

Try this one.

Sub dash2()
Dim r As Range: Set r = Range(ActiveCell, ActiveCell.End(xlDown))
Dim AR() As Variant: AR = r.Value
Dim a As String

With Application.WorksheetFunction
    For i = LBound(AR) To UBound(AR)
        a = AR(i, 1)
        If InStr(a, "-") = 0 Then AR(i, 1) = .Replace(.Replace(a, 5, 0, "-"), Len(a) - 3, 0, "-")
    Next i
End With

r.Value = AR
End Sub

Irobbo314, SUCCESS! This worked PERFECTLY!
Thank you so much for your help - this will save me so much work!
I really appreciate you!

Paul

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂

 

[TotallyConfused]

Hello Irobbo314

I'd like to ask a couple of followup questions if I may, concerning the following line of code you used.

If InStr(a, "-") = 0 Then AR(i, 1) = .Replace(.Replace(a, 5, 0, "-"), Len(a) - 3, 0, "-")

1) Why did you use two 'Replace' commands? Is it because you had to insert two dashes?
2 ) I know 'Len(a)' will give the length of the variable 'a' which in this case is a row in the array. What I don't understand is that whole section of code, starting with: Len(a) -3,0,"-". What does the '-3' mean?

I set up a worksheet using Pgiering's sample data. Your program worked perfectly. Good job! I appreciate any help you may offer in clearing up this puzzle for me.

TotallyConfused

Edit: I just now figured out what that '-3' stands for. It inserts the second '-' in the 'len(a)-3' position.