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?

 

[Per Jessen]

Hi Roger

Paste this event code in the code sheet for the desired sheet.

Private Sub Worksheet_Calculate()
Range("A2:A8").ClearContents
MyArr = Split(Range("A1").Value, "-")
For r = LBound(MyArr) To UBound(MyArr)
Range("A1").Offset(r + 1, 0) = MyArr(r)
Next
End Sub

Regards,
Per

 

[Roger on Excel]

Dear Per,

Many thanks for this, however, when I paste it into the sheet VBA page, it
does not seem to work - what do you think?

Regards,

Roger

Hi Roger

Paste this event code in the code sheet for the desired sheet.

Private Sub Worksheet_Calculate()
Range("A2:A8").ClearContents
MyArr = Split(Range("A1").Value, "-")
For r = LBound(MyArr) To UBound(MyArr)
Range("A1").Offset(r + 1, 0) = MyArr(r)
Next
End Sub

Regards,
Per

[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?