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

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
 
R

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
 
R

Roger on Excel

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...
 
R

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)


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...
 
R

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)


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...
 
R

Roger on Excel

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

using LEN / LEFT / MID / FIND functions to create a list 2
Move data from different columns into one column, but different rows. 2
remove duplicate rows from specific data in a column; excel 2007 1
VBA - Error Using Mid To Parse A Data (.dat) File 4
Using concatenate to create a multiline with references to cells 3
statistic using excel data pls help!!!!! 3
Create a separator line using conditional formatting 3
How to create a list of permutations and combinations? 2

Top