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

  • Thread starter Thread starter Roger on Excel
  • Start date Start date
R

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
 
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
 
Thanks Rick - this works nicely - is there a way for it to do it
automatically?

Regards,

Roger

Rick Rothstein said:
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 said:
[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
Click to expand...
Click to expand...
 
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 said:
Thanks Rick - this works nicely - is there a way for it to do it
automatically?

Regards,

Roger

Rick Rothstein said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Thanks Rick - this works nicely - is there a way for it to do it
automatically?

Regards,

Roger

Rick Rothstein said:
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
Click to expand...
Click to expand...
Click to expand...
 
Many Thanks Rick,

Works great !

Best regards,

Roger



Rick Rothstein said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top