Macro to expand a range of alphanumeric text

B

blooze_hound

New Member
Joined
Dec 31, 2013
Messages
2
Hello,
I'm looking for a macro for Excel 2007 to expand alphanumeric text contained in a column. For example cell A:1 may contain TP1-6 TP30-TP34 which would be expanded into TP1, TP2, TP3, TP4, TP5, TP6, TP30, TP31, TP32, TP33, TP34 and placed into an empty cell in B:1. Cell A:2 may contain X1-X19, expanded to X1, X2, X3, X4, etc and placed into an empty cell in B:2. Any cells without ranges it would need to copy the contents to the adjacent empty cell. This would then be repeated for the entire column with varying text before the numbers (R12-23, JP3-7 etc) until a blank cell is reached. The range will always be delineated with the dash.
Thanks in advance for your help.

Excel 2007
AB
1TP1-6 TP30-34TP1, TP2, TP3, TP4, TP5, TP6, TP30, TP31, TP32, TP33, TP34
2R1R1
3R13, R14, R15, R16R13, R14, R15, R16
4X19-X20X19,X20
5R3 R9 R10 R26R3 R9 R10 R26

<tbody>
</tbody>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
TP1-6, why not TP1-TP6 'cos you used that in cell A4 which is X19-X20; if they are uniform across board, it might be easier to a get macro...although am still trying to wrap my head around it at the moment
 
Upvote 0
blooze_hound said:
Hello,
I'm looking for a macro for Excel 2007 to expand alphanumeric text contained in a column. For example cell A:1 may contain TP1-6 TP30-TP34 which would be expanded into TP1, TP2, TP3, TP4, TP5, TP6, TP30, TP31, TP32, TP33, TP34 and placed into an empty cell in B:1. Cell A:2 may contain X1-X19, expanded to X1, X2, X3, X4, etc and placed into an empty cell in B:2. Any cells without ranges it would need to copy the contents to the adjacent empty cell. This would then be repeated for the entire column with varying text before the numbers (R12-23, JP3-7 etc) until a blank cell is reached. The range will always be delineated with the dash.
Thanks in advance for your help.

Excel 2007
AB
1TP1-6 TP30-34TP1, TP2, TP3, TP4, TP5, TP6, TP30, TP31, TP32, TP33, TP34
2R1R1
3R13, R14, R15, R16R13, R14, R15, R16
4X19-X20X19,X20
5R3 R9 R10 R26R3 R9 R10 R26

<tbody>
</tbody>
Click to expand...

First off, I am ignoring your different ways of delimiting the list (sometimes comma/space, sometimes comma only, sometimes space) and using comma/space between each part of the expanded series. With that said, give this macro a try...

Code: 
Function ExpandedSeries(ByVal S As String) As String
  Dim X As Long, Z As Long
  Dim Letter As String, NumberLeft As String, NumberRight As String, Parts() As String
  S = Application.Trim(Replace(S, ",", " "))
  Parts = Split(S)
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*-*" Then
      For Z = 1 To InStr(Parts(X), "-") - 1
        If IsNumeric(Mid(Parts(X), Z, 1)) Then
          Letter = Left(Parts(X), Z + (Left(Parts(X), 1) Like "[A-Za-z]"))
          If IsNumeric(Letter) Then Letter = ""
          NumberLeft = Mid(Left(Parts(X), InStr(Parts(X), "-") - 1), Z, 99)
          NumberRight = Replace(Mid(Parts(X), InStr(Parts(X), "-") + 1), Letter, "")
          Exit For
        End If
      Next
      For Z = NumberLeft To NumberRight
        ExpandedSeries = ExpandedSeries & ", " & Letter & Z
      Next
    Else
      ExpandedSeries = ExpandedSeries & ", " & Parts(X)
    End If
  Next
  ExpandedSeries = Mid(ExpandedSeries, 3)
End Function
 
Last edited:
Upvote 0
Rick Rothstein said:
First off, I am ignoring your different ways of delimiting the list (sometimes comma/space, sometimes comma only, sometimes space) and using comma/space between each part of the expanded series. With that said, give this macro a try...

Rich (BB code): 
Function ExpandedSeries(ByVal S As String) As String
  Dim X As Long, Z As Long
  Dim Letter As String, NumberLeft As String, NumberRight As String, Parts() As String
  S = Application.Trim(Replace(S, ",", " "))
  Parts = Split(S)
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*-*" Then
      For Z = 1 To InStr(Parts(X), "-") - 1
        If IsNumeric(Mid(Parts(X), Z, 1)) Then
          Letter = Left(Parts(X), Z + (Left(Parts(X), 1) Like "[A-Za-z]"))
          If IsNumeric(Letter) Then Letter = ""
          NumberLeft = Mid(Left(Parts(X), InStr(Parts(X), "-") - 1), Z, 99)
          NumberRight = Replace(Mid(Parts(X), InStr(Parts(X), "-") + 1), Letter, "")
          Exit For
        End If
      Next
      For Z = NumberLeft To NumberRight
        ExpandedSeries = ExpandedSeries & ", " & Letter & Z
      Next
    Else
      ExpandedSeries = ExpandedSeries & ", " & Parts(X)
    End If
  Next
  ExpandedSeries = Mid(ExpandedSeries, 3)
End Function
Click to expand...
I am posting the complete UDF, but the only change I made is in the line highlighted in red above... the change allows the text to have one or more spaces on either side of any dashes as well as how the OP presented the text (with no spaces on either side of the dash)... this change should make the function more universally usable.

Rich (BB code): 
Function ExpandedSeries(ByVal S As String) As String
  Dim X As Long, Z As Long
  Dim Letter As String, NumberLeft As String, NumberRight As String, Parts() As String
  S = Replace(Replace(Application.Trim(Replace(S, ",", " ")), " -", "-"), "- ", "-")
  Parts = Split(S)
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*-*" Then
      For Z = 1 To InStr(Parts(X), "-") - 1
        If IsNumeric(Mid(Parts(X), Z, 1)) Then
          Letter = Left(Parts(X), Z + (Left(Parts(X), 1) Like "[A-Za-z]"))
          If IsNumeric(Letter) Then Letter = ""
          NumberLeft = Mid(Left(Parts(X), InStr(Parts(X), "-") - 1), Z, 99)
          NumberRight = Replace(Mid(Parts(X), InStr(Parts(X), "-") + 1), Letter, "")
          Exit For
        End If
      Next
      For Z = NumberLeft To NumberRight
        ExpandedSeries = ExpandedSeries & ", " & Letter & Z
      Next
    Else
      ExpandedSeries = ExpandedSeries & ", " & Parts(X)
    End If
  Next
  ExpandedSeries = Mid(ExpandedSeries, 3)
End Function
 
Upvote 0
blooze_hound,

Welcome to the MrExcel forum.


Rick, nicely done. I hope you do not mind, but, I created a macro to put your function results directly into column B.


Sample raw data:


Excel 2007
AB
1TP1-6 TP30-34
2R1
3R13, R14, R15, R16
4X19-X20
5R3 R9 R10 R26
6TP1-6
7TP1-6 TP30-34 ZZ1-5
8
Sheet1


After the macro with the function:


Excel 2007
AB
1TP1-6 TP30-34TP1, TP2, TP3, TP4, TP5, TP6, TP30, TP31, TP32, TP33, TP34
2R1R1
3R13, R14, R15, R16R13, R14, R15, R16
4X19-X20X19, X20
5R3 R9 R10 R26R3, R9, R10, R26
6TP1-6TP1, TP2, TP3, TP4, TP5, TP6
7TP1-6 TP30-34 ZZ1-5TP1, TP2, TP3, TP4, TP5, TP6, TP30, TP31, TP32, TP33, TP34, ZZ1, ZZ2, ZZ3, ZZ4, ZZ5
8
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code - the macro and the function
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub ReorgData()
' hiker95, 01/01/2014
' http://www.mrexcel.com/forum/excel-questions/747690-macro-expand-range-alphanumeric-text.html
With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
  .FormulaR1C1 = "=ExpandedSeries(RC[-1])"
  .Value = .Value
End With
Columns(2).AutoFit
End Sub
Function ExpandedSeries(ByVal S As String) As String
' Rick Rothstein, MrExcel MVP, 12/31/2014
' http://www.mrexcel.com/forum/excel-questions/747690-macro-expand-range-alphanumeric-text.html
Dim X As Long, Z As Long
Dim Letter As String, NumberLeft As String, NumberRight As String, Parts() As String
S = Replace(Replace(Application.Trim(Replace(S, ",", " ")), " -", "-"), "- ", "-")
Parts = Split(S)
For X = 0 To UBound(Parts)
  If Parts(X) Like "*-*" Then
    For Z = 1 To InStr(Parts(X), "-") - 1
      If IsNumeric(Mid(Parts(X), Z, 1)) Then
        Letter = Left(Parts(X), Z + (Left(Parts(X), 1) Like "[A-Za-z]"))
        If IsNumeric(Letter) Then Letter = ""
        NumberLeft = Mid(Left(Parts(X), InStr(Parts(X), "-") - 1), Z, 99)
        NumberRight = Replace(Mid(Parts(X), InStr(Parts(X), "-") + 1), Letter, "")
        Exit For
      End If
    Next
    For Z = NumberLeft To NumberRight
      ExpandedSeries = ExpandedSeries & ", " & Letter & Z
    Next
  Else
    ExpandedSeries = ExpandedSeries & ", " & Parts(X)
  End If
Next
ExpandedSeries = Mid(ExpandedSeries, 3)
End Function

Before you use the macro, and, the function, with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
 
Last edited:
Upvote 0
Rick Rothstein said:
I am posting the complete UDF, but the only change I made is in the line highlighted in red above... the change allows the text to have one or more spaces on either side of any dashes as well as how the OP presented the text (with no spaces on either side of the dash)... this change should make the function more universally usable.

Code: 
Function ExpandedSeries(ByVal S As String) As String
  Dim X As Long, Z As Long
  Dim Letter As String, NumberLeft As String, NumberRight As String, Parts() As String
  S = Replace(Replace(Application.Trim(Replace(S, ",", " ")), " -", "-"), "- ", "-")
  Parts = Split(S)
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*-*" Then
      For Z = 1 To InStr(Parts(X), "-") - 1
        If IsNumeric(Mid(Parts(X), Z, 1)) Then
          Letter = Left(Parts(X), Z + (Left(Parts(X), 1) Like "[A-Za-z]"))
          If IsNumeric(Letter) Then Letter = ""
          NumberLeft = Mid(Left(Parts(X), InStr(Parts(X), "-") - 1), Z, 99)
          NumberRight = Replace(Mid(Parts(X), InStr(Parts(X), "-") + 1), Letter, "")
          Exit For
        End If
      Next
      For Z = NumberLeft To NumberRight
        ExpandedSeries = ExpandedSeries & ", " & Letter & Z
      Next
    Else
      ExpandedSeries = ExpandedSeries & ", " & Parts(X)
    End If
  Next
  ExpandedSeries = Mid(ExpandedSeries, 3)
End Function
Click to expand...

One more change, not in functionality, but rather to just simplify the code a little bit. I have removed the NumberLeft and NumberRight variables and replaced them with a Numbers() dynamic array instead... this allowed me to use a simpler expression to break the numbers apart for use in the For..Next loop that expands them into a series. Here is the new, modified code...

Code: 
Function ExpandedSeries(ByVal S As String) As String
  Dim x As Long, Z As Long
  Dim Letter As String, Numbers() As String, Parts() As String
  S = Replace(Replace(Application.Trim(Replace(S, ",", " ")), " -", "-"), "- ", "-")
  Parts = Split(S)
  For x = 0 To UBound(Parts)
    If Parts(x) Like "*-*" Then
      For Z = 1 To InStr(Parts(x), "-") - 1
        If IsNumeric(Mid(Parts(x), Z, 1)) Then
          Letter = Left(Parts(x), Z + (Left(Parts(x), 1) Like "[A-Za-z]"))
          If IsNumeric(Letter) Then Letter = ""
          Exit For
        End If
      Next
      Numbers = Split(Replace(Parts(x), Letter, ""), "-")
      For Z = Numbers(0) To Numbers(1)
        ExpandedSeries = ExpandedSeries & ", " & Letter & Z
      Next
    Else
      ExpandedSeries = ExpandedSeries & ", " & Parts(x)
    End If
  Next
  ExpandedSeries = Mid(ExpandedSeries, 3)
End Function
 
Upvote 0
Rick Rothstein said:
One more change, not in functionality, but rather to just simplify the code a little bit. I have removed the NumberLeft and NumberRight variables and replaced them with a Numbers() dynamic array instead... this allowed me to use a simpler expression to break the numbers apart for use in the For..Next loop that expands them into a series. Here is the new, modified code...

Rich (BB code): 
Function ExpandedSeries(ByVal S As String) As String
  Dim x As Long, Z As Long
  Dim Letter As String, Numbers() As String, Parts() As String
  S = Replace(Replace(Application.Trim(Replace(S, ",", " ")), " -", "-"), "- ", "-")
  Parts = Split(S)
  For x = 0 To UBound(Parts)
    If Parts(x) Like "*-*" Then
      For Z = 1 To InStr(Parts(x), "-") - 1
        If IsNumeric(Mid(Parts(x), Z, 1)) Then
          Letter = Left(Parts(x), Z + (Left(Parts(x), 1) Like "[A-Za-z]"))
          If IsNumeric(Letter) Then Letter = ""
          Exit For
        End If
      Next
      Numbers = Split(Replace(Parts(x), Letter, ""), "-")
      For Z = Numbers(0) To Numbers(1)
        ExpandedSeries = ExpandedSeries & ", " & Letter & Z
      Next
    Else
      ExpandedSeries = ExpandedSeries & ", " & Parts(x)
    End If
  Next
  ExpandedSeries = Mid(ExpandedSeries, 3)
End Function
Click to expand...
One more change and I am done... I promise.:eek: If you replace the line I highlighted in red above with the following line of code, the code will be able to handle ascending series as it does now, and descending ones as well (e.g., AB9-AB5).

Rich (BB code): 
For Z = Numbers(0) To Numbers(1) Step Sgn(-(Numbers(1) > Numbers(0)) - 0.5

For those who might be interested, I turned this UDF into a mini-blog article...

<!-- title / author block -->Generalized Series Expansions (e.g. AB5-AB9 becomes AB5, AB6, AB7, AB8, AB9)
 
Last edited:
Upvote 0
Thanks for both your help, the code works amazingly well. If I save it as a module on the worksheet I'm working on it works flawlessly. However if I save it to my Personal workbook it throws up a #NAME! error.
 
Upvote 0
... and another one, that works OK with the examples:

Code: 
Function ExpandSeries(s As String) As String
Dim j As Long
Dim v As Variant

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b([A-Z]+)(\d+)-(?:\1)?(\d+)\b"
    v = Split(.Replace(Application.Trim(Replace(s, ",", " ")), """$1""&row($2:$3)"))
    For j = 0 To UBound(v)
        If v(j) Like "*:*" Then v(j) = Join(Application.Transpose(Evaluate(v(j))), ", ")
    Next j
End With
ExpandSeries = Join(v, ", ")
End Function



<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">TP1-6 TP30-34</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">TP1, TP2, TP3, TP4, TP5, TP6, TP30, TP31, TP32, TP33, TP34</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">R1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">R1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">R13, R14, R15, R16</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">R13, R14, R15, R16</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X19-X20</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X19, X20</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">R3 R9 R10 R26</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">R3, R9, R10, R26</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">TP1-6</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">TP1, TP2, TP3, TP4, TP5, TP6</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">TP1-6 TP30-34 ZZ1-5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">TP1, TP2, TP3, TP4, TP5, TP6, TP30, TP31, TP32, TP33, TP34, ZZ1, ZZ2, ZZ3, ZZ4, ZZ5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>8</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [book1]Sheet1</td></tr></table>
 
Upvote 0
pgc01 said:
... and another one, that works OK with the examples:

Code: 
Function ExpandSeries(s As String) As String
Dim j As Long
Dim v As Variant

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b([A-Z]+)(\d+)-(?:\1)?(\d+)\b"
    v = Split(.Replace(Application.Trim(Replace(s, ",", " ")), """$1""&row($2:$3)"))
    For j = 0 To UBound(v)
        If v(j) Like "*:*" Then v(j) = Join(Application.Transpose(Evaluate(v(j))), ", ")
    Next j
End With
ExpandSeries = Join(v, ", ")
End Function
Click to expand...
Does not appear to handle a wild-and-wooly text string like this one whereas my latest revised version does...

Code: 
a9 - 4 x4,m9 r7 b4 - b8,c4-1

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Get min value in floating range
Replies
9
Views
310
Peter_SSs
Peter_SSs
M
  • Solved
Update formula on mouse click without macro
Replies
3
Views
211
Fluff
Fluff
D
  • Question
Organizing data for a textbook order workbook
Replies
0
Views
162
djaida
D
redspanna
  • Question
Show next date in RANK if same value found
Replies
2
Views
361
redspanna
redspanna
U
  • Question
Chart creation - best presentation?
Replies
2
Views
314
Fluff
Fluff

Forum statistics

Threads
1,214,885
Messages
6,122,090
Members
449,065
Latest member
Danger_SF

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top