Editing alphanumeric String (with $ like separator)

[Maurizio]

Hi all,

I have this data:

169$XRIF 320246/16
XRIF 304641/17
PRY 609295/17 TON 22,34$PRY 609296/17 TON 4,62
380$DUC 160370/17$DUC 160371/17

how can I get this (with vba):

169
XRIF 320246/16
XRIF 304641/17
PRY 609295/17 TON 22,34
PRY 609296/17 TON 4,62
380
DUC 160370/17
DUC 160371/17


Tia. Mau

 

[smozgur]

Hi, Maurizio


Following code takes the selected range as input, and distributes the values into the adjacent column as delimited with "$" string. I assume your sample data is in A1:A4.


The key method is Split, which takes an expression as the first parameter, and a delimiter string as the second parameter. Then you loop through the split value, and place the each delimited value into the next row in the adjacent column.

i and j counters might look confusing at first, that's the way I find the next row for this code. You can choose your own way to find the next empty cell.

Sub parseWithSeparator()
Dim rng As Range
Dim cll As Range
Dim i, j As Integer


    Set rng = Selection
    For Each cll In rng.Cells
        i = 0
        For Each res In Split(cll.Value, "$")
            i = i + 1
            rng.Cells(i + j, 1).Offset(, 1).Value = res
        Next res
        j = j + i
    Next cll
End Sub

Hope it helps.

Suat

 

[JoeMo]

This will do the editing in place. First select the cells you want to edit, then run the macro:

Sub Maurizio()
'select the data cells to be edited first, then run this macro
Dim R As Range, i As Long, N As Long, x As Variant
Set R = Selection
Application.ScreenUpdating = False
For i = R.Rows.Count To 1 Step -1
    N = Len(R(i)) - Len(Replace(R(i), "$", ""))
    If N = 0 Then GoTo Nx
    R(i).Offset(1, 0).Resize(N, 1).Insert shift:=xlDown
    x = R(i)
    For j = 1 To R(i).Resize(N, 1).Count + 1
        R(i)(j) = Split(x, "$")(j - 1)
    Next j
Nx:
Next i
Application.ScreenUpdating = True
End Sub

 

[Maurizio]

Hi, Maurizio


Following code takes the selected range as input, and distributes the values into the adjacent column as delimited with "$" string. I assume your sample data is in A1:A4.


The key method is Split, which takes an expression as the first parameter, and a delimiter string as the second parameter. Then you loop through the split value, and place the each delimited value into the next row in the adjacent column.

i and j counters might look confusing at first, that's the way I find the next row for this code. You can choose your own way to find the next empty cell.

Sub parseWithSeparator()
Dim rng As Range
Dim cll As Range
Dim i, j As Integer


    Set rng = Selection
    For Each cll In rng.Cells
        i = 0
        For Each res In Split(cll.Value, "$")
            i = i + 1
            rng.Cells(i + j, 1).Offset(, 1).Value = res
        Next res
        j = j + i
    Next cll
End Sub

Hope it helps.

Suat

tnks.

 

[Rick Rothstein]

Assuming your outputted list will have less than 65,500+ rows of data, you can also use this non-looping macro as well...

[table="width: 500"]
[tr]
	[td]Sub SplitDataAtDollarSign()
  Dim Arr As Variant
  Arr = Split(Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), "$"), "$")
  Range("B1").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub[/td]
[/tr]
[/table]

 

[Maurizio]

Assuming your outputted list will have less than 65,500+ rows of data, you can also use this non-looping macro as well...

[table="width: 500"]
[tr]
	[td]Sub SplitDataAtDollarSign()
  Dim Arr As Variant
  Arr = Split(Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), "$"), "$")
  Range("B1").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub[/td]
[/tr]
[/table]

Fine, thanks.

 

[Maurizio]

Good. Thanks Joe.