SPLIT A STRING THAT HAS NO DELIMITERS, SUCH AS M2750012ML2T23 INTO SUB-STRINGS USING VBA; THE SPLIT COULD BE DIVIDED LIKE: M27500 12 ML 2 T 23

[Code_Ninja]

HOW TO SPLIT A STRING THAT HAS NO DELIMITERS, SUCH AS M2750012ML2T23 INTO SUB-STRINGS USING VBA; THE SPLIT COULD BE DIVIDED LIKE LIKE THE FOLLOWING: M27500 12 ML 2 T 23. EACH SUB-STRING COULD BE PUT IN SEPARATE CELL OR COLUMNS.

I AM ASSUMING THAT YOU COULD USE SOME KIND OF LOOP WITH A PATTERN, OR A LOOP THAT INCREMENTS EACH TIME YOU ENCOUNTER TEXT AND ONE EACH TIME YOU ENCOUNTER NUMBERS IF YOU WANT THEM SPLIT APART THAT WAY.

THANK YOU, CODE_NINJA,

 

[jolivanes]

Sub Maybe()
Dim sArr, lArr, tArr, c As Range, i As Long
sArr = Array(1, 7, 9, 11, 12, 13)
lArr = Array(6, 2, 2, 1, 1, 2)
ReDim tArr(1 To 6)
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        For i = LBound(sArr) To UBound(sArr)
            tArr(i + 1) = Mid(c, sArr(i), lArr(i))
        Next i
        c.Offset(, 1).Resize(, UBound(tArr)) = tArr
        ReDim tArr(1 To 6)
    Next c
End Sub

 

[Rick Rothstein]

One example is not to know if a solution is the most efficient or not. Are all the text strings the same length with letters and digits (no matter what the letters and digits are) in the same location. In other words if A represents a letter and N a digit, is the shape of all your text strings ANNNNNNNAANANN? If so...

Sub SplitNoDelims()
  Dim R As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
  For R = 1 To UBound(Data)
    Data(R, 1) = Format(Trim(Replace(Data(R, 1), Chr(160), " ")), "@@@@@@ @@ @@ @ @ @@")
  Next
  Range("B1").Resize(UBound(Data)) = Data
  Columns("B").TextToColumns , xlDelimited, , , False, False, False, True, False
End Sub

 

[Code_Ninja]

One example is not to know if a solution is the most efficient or not. Are all the text strings the same length with letters and digits (no matter what the letters and digits are) in the same location. In other words if A represents a letter and N a digit, is the shape of all your text strings ANNNNNNNAANANN? If so...

Sub SplitNoDelims()
  Dim R As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
  For R = 1 To UBound(Data)
    Data(R, 1) = Format(Trim(Replace(Data(R, 1), Chr(160), " ")), "@@@@@@ @@ @@ @ @ @@")
  Next
  Range("B1").Resize(UBound(Data)) = Data
  Columns("B").TextToColumns , xlDelimited, , , False, False, False, True, False
End Sub

Hello Rick; Thanks for the quick reply.
This is my first time posting on this Forum as I am a new member.
To your question regarding the the length of the Text Strings; yes, the length and format will be the same in this case, this happens to be a type of Electrical Cable that I want to be able to use VBA to get the dimensions for in order to size the proper Solder Sleeve to connect it to other Wires.

In this case, M2750012ML2T23 actually looks like this: M27500-12ML2T23 (I kept the Dash out to keep it simple).

The breakout will be the following:

M27550 = CABLE TYPE
12 = THE GAUGE OF THE WIRE
ML = THE BASIC WIRE TYPE (AS81044/12 IN THIS CASE)
2 = THE NUMBER OF WIRES (CONDUCTORS IN THIS CABLE) NOTE: IT COULD HAVE 2, 3, OR 4 WIRES
T = SHIELD TYPE (ROUND TIN COPPER SHIELD WHICH COVERS BOTH WIRES)
23 = THE OUTER JACKET MATERIAL (XLETFE)

By splitting the Cable designation into Sub-Strings, calculation can then be done on the 12, ML Wire Diameter, 2 - overall size of 2 Wires, T - the thickness of the Shield, and 23 - the thickness of the Cable Jacket (Overall Cable covering for the 2 Wires),

The first step is being able to Split the Cable apart. Other Cables could have different length Sub-Strings, but in this case, here are a few more examples of this cable:

M27500-16ML4T23
M27500-18TG2T14
M27500-14SD2T06

Right now, people where I work size the different Cables manually, but I want automate things by having a User put the Cable Designation in a UserForm, click a Button and the program will split the Cable apart, run the necessary calculations on those pieces and automatically size the correct Solder Sleeve to to use in order to Splice 2 or more Wires together.

Again, thanks for the help, and I hope that you get what I am trying to do.

Code_Ninja.