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:

[TABLE="width: 1757"]
<tbody>[TR]
[TD]Column A: Original Message[/TD]
[TD]Column B: First 40 Characters[/TD]
[TD]Column C: Remaining Characters[/TD]
[TD]Len (A)[/TD]
[TD]Len (B)[/TD]
[TD]Len (C )[/TD]
[/TR]
[TR]
[TD]Thank you for registering at the MrExcel Message Board. Before we can activate your account one last step.[/TD]
[TD]Thank you for registering at the MrExcel[/TD]
[TD]Message Board. Before we can activate your account one last step.[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]Please be sure not to add extra spaces. You will need to type in your username and activation number on the page.[/TD]
[TD]Please be sure not to add extra spaces.[/TD]
[TD]You will need to type in your username and activation number on the page.[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]73[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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