PDA

View Full Version : [SOLVED] expand and complete series excel



DLW
08-03-2018, 04:56 AM
Good Morning,
I have an excel file that contains a column of reference designators (electronics circuit card location references).
i need a way of replacing the "short hand version" and expanding and completing the series...
eg
a cell contains C1-3 C5 C7-8 would like to change/expand it to C1 C2 C3 C5 C7 C8.
Each row can contain a different number of entries and different prefix ie R34-120 or FER1-36

i am running office 365 and have some experience with VBA...
here is a sample of the data




REF DES


C60 C65 C70-74 C81


C53


C59 C64 C69 C80


C2-3 C5 C7 C11 C13 C15 C19-20 C29 C32 C34 C38 C40 C42-49 C51 C54-58 C61-63 C66-68 C77-79 C88 C90 C92 C94 C98-134 C138 C164 C166 C168 C170 C172 C174 C176 C178 C180 C182 C184 C186 C188 C190 C192 C194 C196 C198 C200 C202 C204 C206


J34-37


TP31-32 TP34-35 TP37-38 TP40-41


FER1-36


FB15-16


U2 U6-11 U13-16 U18-19 U28-29 U62-69 U74-86




any help would be greatly apprecieated

thanks
dave

p45cal
08-03-2018, 05:58 AM
In the attached, hurried and basic function Expand. No checks made.
Will fall over if unexpected input.
Function Expand(g)
myNumerals = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9") 'could've used instr instead
If TypeName(g) = "Range" Then
x = Split(Application.Trim(g.Value))
Else
x = Split(Application.Trim(g))
End If
For i = LBound(x) To UBound(x)
y = Split(x(i), "-")
If UBound(y) = 1 Then
p = 0
Do
p = p + 1
Loop Until Not IsError(Application.Match(Mid(y(0), p, 1), myNumerals, 0))
Prefix = Left(y(0), p - 1)
myStart = CLng(Mid(y(0), p))
myFinish = CLng(y(1))
For j = myStart To myFinish
myStr = myStr & " " & Prefix & CStr(j)
Next j
Else
myStr = myStr & " " & x(i)
End If
Next i
Expand = Application.Trim(myStr)
End Function

DLW
08-03-2018, 06:29 AM
Thank you very much p45cal!!!
this is exactly what i was hoping for...
will mark as solved once i incorporate into larger code...

thanks again for such a quick response