# 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

##### New Member
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,

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Code:
``````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``````

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...
VBA Code:
``````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``````

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...
VBA Code:
``````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.

#### Attachments

• M2700 CABLE.PNG
97.6 KB · Views: 8

Replies
0
Views
227
Replies
3
Views
251
Replies
1
Views
1K
Replies
1
Views
260
Replies
3
Views
262

1,207,402
Messages
6,078,270
Members
446,324
Latest member
JKamlet

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

### Which adblocker are you using?

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

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