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

[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

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

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)


"Roger on Excel" wrote in 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

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

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

Regards,

Roger

"Rick Rothstein" wrote:

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)


"Roger on Excel" wrote in 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

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

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)


"Roger on Excel" wrote in message
...

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

Regards,

Roger

"Rick Rothstein" wrote:

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)


"Roger on Excel" wrote in
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

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

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)


"Rick Rothstein" wrote in message
...
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)


"Roger on Excel" wrote in message
...

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

Regards,

Roger

"Rick Rothstein" wrote:

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)


"Roger on Excel" wrote in
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

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

Many Thanks Rick,

Works great !

Best regards,

Roger



"Rick Rothstein" wrote:

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)


"Rick Rothstein" wrote in message
...
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)


"Roger on Excel" wrote in message
...

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

Regards,

Roger

"Rick Rothstein" wrote:

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)


"Roger on Excel" wrote in
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