using LEN / LEFT / MID / FIND functions to create a list

[Roger on Excel]

[Excel 2003]

I have a vlookup which populates cell $A$1 with codes separated by "-". For
example:

"A3-C-D4-F5"

or

"A-C-D5"

or even

"C"

I would like these to be processed so that
on the rows below $A$1, the idividual items are separated out line by line,
such that

A3-C-D4-F5 would be listed as

A3
C
D4
F5

in the cells below. The codes have "-" in various positions (or none at all
in the case of a single item) and can have up to 7 codes in a string.

Sounds complicated..

Can anyone help me separate the individual items from a hyphenized list of
codes as described above?

Thanks, Roger

 

[Rick Rothstein]

This macro will do what you asked for...

Sub SplitText()
Dim X As Long, S() As String
With Range("A1")
S = Split(.Value, "-")
For X = 0 To UBound(S)
.Offset(X + 1).Value = S(X)
Next
End With
End Sub

 

[Roger on Excel]

Thanks Rick - this works nicely - is there a way for it to do it
automatically?

Regards,

Roger

This macro will do what you asked for...

Sub SplitText()
Dim X As Long, S() As String
With Range("A1")
S = Split(.Value, "-")
For X = 0 To UBound(S)
.Offset(X + 1).Value = S(X)
Next
End With
End Sub

--
Rick (MVP - Excel)

[Excel 2003]

I have a vlookup which populates cell $A$1 with codes separated by "-".
For
example:

"A3-C-D4-F5"

or

"A-C-D5"

or even

"C"

I would like these to be processed so that
on the rows below $A$1, the idividual items are separated out line by
line,
such that

A3-C-D4-F5 would be listed as

A3
C
D4
F5

in the cells below. The codes have "-" in various positions (or none at
all
in the case of a single item) and can have up to 7 codes in a string.

Sounds complicated..

Can anyone help me separate the individual items from a hyphenized list of
codes as described above?

Thanks, Roger

 

[Rick Rothstein]

Try this... right click the tab at the bottom of the worksheet where you
want this functionality, select View Code from the popup menu that appears
and Copy/Paste the following into the code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, R As Range, S() As String
For Each R In Target.Dependents
If R.Address = "$A$1" Then
Application.EnableEvents = False
With Range("A1")
.Offset(1).Resize(7).Clear
S = Split(.Value, "-")
For X = 0 To UBound(S)
.Offset(X + 1).Value = S(X)
Next
End With
Application.EnableEvents = True
Exit Sub
End If
Next
End Sub

Now, when you make a change to any cell referenced in the VLOOKUP formula in
A1, the value A1 evaluates to should be processed as you wanted.

--
Rick (MVP - Excel)

Thanks Rick - this works nicely - is there a way for it to do it
automatically?

Regards,

Roger

This macro will do what you asked for...

Sub SplitText()
Dim X As Long, S() As String
With Range("A1")
S = Split(.Value, "-")
For X = 0 To UBound(S)
.Offset(X + 1).Value = S(X)
Next
End With
End Sub

--
Rick (MVP - Excel)


message

[Excel 2003]

I have a vlookup which populates cell $A$1 with codes separated by "-".
For
example:

"A3-C-D4-F5"

or

"A-C-D5"

or even

"C"

I would like these to be processed so that
on the rows below $A$1, the idividual items are separated out line by
line,
such that

A3-C-D4-F5 would be listed as

A3
C
D4
F5

in the cells below. The codes have "-" in various positions (or none
at
all
in the case of a single item) and can have up to 7 codes in a string.

Sounds complicated..

Can anyone help me separate the individual items from a hyphenized list
of
codes as described above?

Thanks, Roger

 

[Rick Rothstein]

There is a problem with the code I posted earlier; the following should work
correctly (install it the same way)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, R As Range, TD As Range, S() As String
On Error GoTo NoDependentCells
If Not Target.Dependents Is Nothing Then
For Each R In Target.Dependents
If R.Address = "$A$1" Then
Application.EnableEvents = False
With Range("A1")
.Offset(1).Resize(7).Clear
S = Split(.Value, "-")
For X = 0 To UBound(S)
.Offset(X + 1).Value = S(X)
Next
End With
Application.EnableEvents = True
Exit Sub
End If
Next
End If
NoDependentCells:
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)

Try this... right click the tab at the bottom of the worksheet where you
want this functionality, select View Code from the popup menu that appears
and Copy/Paste the following into the code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, R As Range, S() As String
For Each R In Target.Dependents
If R.Address = "$A$1" Then
Application.EnableEvents = False
With Range("A1")
.Offset(1).Resize(7).Clear
S = Split(.Value, "-")
For X = 0 To UBound(S)
.Offset(X + 1).Value = S(X)
Next
End With
Application.EnableEvents = True
Exit Sub
End If
Next
End Sub

Now, when you make a change to any cell referenced in the VLOOKUP formula
in A1, the value A1 evaluates to should be processed as you wanted.

--
Rick (MVP - Excel)

Thanks Rick - this works nicely - is there a way for it to do it
automatically?

Regards,

Roger

This macro will do what you asked for...

Sub SplitText()
Dim X As Long, S() As String
With Range("A1")
S = Split(.Value, "-")
For X = 0 To UBound(S)
.Offset(X + 1).Value = S(X)
Next
End With
End Sub

--
Rick (MVP - Excel)


message
[Excel 2003]

I have a vlookup which populates cell $A$1 with codes separated by
"-".
For
example:

"A3-C-D4-F5"

or

"A-C-D5"

or even

"C"

I would like these to be processed so that
on the rows below $A$1, the idividual items are separated out line by
line,
such that

A3-C-D4-F5 would be listed as

A3
C
D4
F5

in the cells below. The codes have "-" in various positions (or none
at
all
in the case of a single item) and can have up to 7 codes in a string.

Sounds complicated..

Can anyone help me separate the individual items from a hyphenized
list of
codes as described above?

Thanks, Roger

 

[Roger on Excel]

Many Thanks Rick,

Works great !

Best regards,

Roger

There is a problem with the code I posted earlier; the following should work
correctly (install it the same way)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, R As Range, TD As Range, S() As String
On Error GoTo NoDependentCells
If Not Target.Dependents Is Nothing Then
For Each R In Target.Dependents
If R.Address = "$A$1" Then
Application.EnableEvents = False
With Range("A1")
.Offset(1).Resize(7).Clear
S = Split(.Value, "-")
For X = 0 To UBound(S)
.Offset(X + 1).Value = S(X)
Next
End With
Application.EnableEvents = True
Exit Sub
End If
Next
End If
NoDependentCells:
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)

Try this... right click the tab at the bottom of the worksheet where you
want this functionality, select View Code from the popup menu that appears
and Copy/Paste the following into the code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, R As Range, S() As String
For Each R In Target.Dependents
If R.Address = "$A$1" Then
Application.EnableEvents = False
With Range("A1")
.Offset(1).Resize(7).Clear
S = Split(.Value, "-")
For X = 0 To UBound(S)
.Offset(X + 1).Value = S(X)
Next
End With
Application.EnableEvents = True
Exit Sub
End If
Next
End Sub

Now, when you make a change to any cell referenced in the VLOOKUP formula
in A1, the value A1 evaluates to should be processed as you wanted.

--
Rick (MVP - Excel)

Thanks Rick - this works nicely - is there a way for it to do it
automatically?

Regards,

Roger

:

This macro will do what you asked for...

Sub SplitText()
Dim X As Long, S() As String
With Range("A1")
S = Split(.Value, "-")
For X = 0 To UBound(S)
.Offset(X + 1).Value = S(X)
Next
End With
End Sub

--
Rick (MVP - Excel)


message
[Excel 2003]

I have a vlookup which populates cell $A$1 with codes separated by
"-".
For
example:

"A3-C-D4-F5"

or

"A-C-D5"

or even

"C"

I would like these to be processed so that
on the rows below $A$1, the idividual items are separated out line by
line,
such that

A3-C-D4-F5 would be listed as

A3
C
D4
F5

in the cells below. The codes have "-" in various positions (or none
at
all
in the case of a single item) and can have up to 7 codes in a string.

Sounds complicated..

Can anyone help me separate the individual items from a hyphenized
list of
codes as described above?

Thanks, Roger