Split String into 2 Columns by 40 characters and 800 characters, Whole Word

j9ortiz

New Member
Joined
Apr 9, 2018
Messages
2
I have 90-120 character strings in Column A. I need Column B to take the first 40 whole word characters and Column C to take the remaining characters up to 800.

I have been researching a solution to this problem and everything I have seen has been slightly off from what I am looking for. I am trying to do this in VBA to stream line the process because I have 30 Spreadsheets each with over 1500 lines.

Example of what I need:

Column A: Original MessageColumn B: First 40 CharactersColumn C: Remaining CharactersLen (A)Len (B)Len (C )
Thank you for registering at the MrExcel Message Board. Before we can activate your account one last step.Thank you for registering at the MrExcelMessage Board. Before we can activate your account one last step.1064065
Please be sure not to add extra spaces. You will need to type in your username and activation number on the page.Please be sure not to add extra spaces.You will need to type in your username and activation number on the page.1134073

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,906
This macro will loop through all your sheets. If there are any sheets in your workbook that you want to exclude, the macro will have to be modified. If this is the case, what are the names of the sheets you want to exclude?
Code:
Sub SplitString()
    Application.ScreenUpdating = False
    Dim rng As Range
    Dim LastRow As Long
    Dim ws As Worksheet
    For Each ws In Sheets
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each rng In ws.Range("A1:A" & LastRow)
            ws.Range("B" & rng.Row) = Left(rng, 40)
            ws.Range("C" & rng.Row) = Mid(rng, 41, 800)
        Next rng
    Next ws
    Application.ScreenUpdating = True
End Sub
 

j9ortiz

New Member
Joined
Apr 9, 2018
Messages
2
Thank you for responding! So I ran this macro and it seems to be cutting some of the words in half. Is there a way to cut it off on a whole word before 40 characters?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,906
Give this a try:
Code:
Sub SplitString()
    Application.ScreenUpdating = False
    Dim count As Long
    Dim rng As Range
    Dim LastRow As Long
    Dim ws As Worksheet
    For Each ws In Sheets
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each rng In ws.Range("A1:A" & LastRow)
            If Mid(rng, 41, 1) <> " " Then
                count = InStrRev(rng, " ", 41)
                ws.Range("B" & rng.Row) = Left(rng, count - 1)
                ws.Range("C" & rng.Row) = Mid(rng, count + 1, 800)
            Else
                ws.Range("B" & rng.Row) = Left(rng, 40)
                ws.Range("C" & rng.Row) = Mid(rng, 41, 800)
            End If
        Next rng
    Next ws
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,826
Messages
5,446,724
Members
405,414
Latest member
wayne_p

This Week's Hot Topics

Top