Insert dashes within character string, within an array.

pgiering

New Member
Joined
Oct 20, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,093
Office Version
  1. 365
Platform
  1. Windows
How about this?

VBA Code:
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,093
Office Version
  1. 365
Platform
  1. Windows
And just for fun a Power Query variant.

Book1
CDE
1Column1Column1
2MSEAVIMA0013SMSEA-VIMA-0013S
3MSEAVIRM0016SMSEA-VIRM-0016S
4MOLPVIRM0017SMOLP-VIRM-0017S
5MOLS130012SMOLS-13-0012S
6MSEAVIRM0011SMSEA-VIRM-0011S
7MOL21300040SMOL2-130-0040S
8MSEAVIMA0008SMSEA-VIMA-0008S
Sheet2


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

pgiering

New Member
Joined
Oct 20, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
How about this?

VBA Code:
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,093
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Like this?

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


VBA Code:
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

New Member
Joined
Oct 20, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,093
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this one.

VBA Code:
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

New Member
Joined
Oct 20, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Try this one.

VBA Code:
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,093
Office Version
  1. 365
Platform
  1. Windows
───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,679
Members
415,921
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top