VBA/Excel: Split column into 40 characters and 800 characters, Whole Word

Question

I have 30-120 character strings in Column A. I need Column B to take up to the first 40 whole word characters and Column C to take the remaining characters 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.

If I have the word Fittings the first i is the 40th character... could the word be pushed into the next column? Leaving 38 characters in the first column

Example of what I am looking for:

If the 40th character is the `a` in the word `cat` do you want to include it or exclude it in A? — Alex K., Apr 09 '18 at 18:02
`=LEFT(A1, 40)` in `B1` and `Right(A1, Len(A1)-40)` in `C1` and copy down? — JNevill, Apr 09 '18 at 18:20
ideally, I would want `cat` to be in the next column. Which would make the first column only 38 characters — Janine Ortiz, Apr 09 '18 at 18:25
Have you considered using [regular expressions](https://stackoverflow.com/a/22542835/111794)? — Zev Spitz, Apr 09 '18 at 19:15

Nicholas Kemp · Answer 1 · 2018-04-09T18:34:02.987

1

So I have this messy code. It might not be (is not) optimal, but it works as you need it to.

Sub SeparateString()

Dim str As String: str = Range("A2")
Dim strLeft, strRight As String
Dim i As Integer: i = 2
Dim lenLeft As Integer

Do While str <> ""
    str = Range("A" & i)
    strLeft = ""
    strRight = ""
    lenLeft = 40


    Do While Mid(str, lenLeft, 1) <> " "
        lenLeft = lenLeft + 1
        If lenLeft > 20000 Then GoTo Out
    Loop
Out:
    strLeft = Left(str, lenLeft)

    On Error Resume Next
        strRight = Right(str, Len(str) - lenLeft)
    On Error GoTo 0

    Range("B" & i) = strLeft
    Range("C" & i) = strRight

    i = i + 1
Loop

End Sub

Output:

edited Apr 09 '18 at 18:34

answered Apr 09 '18 at 18:27

Nicholas Kemp

327
2
17

Is there away to change this to cut off before 40 if the whole word ends in the middle? For example if I have the word `Fittings` the first `i` is the 40th character... could the word be pushed into the next column? Leaving 38 characters in the first column. – Janine Ortiz Apr 09 '18 at 18:43

score 0 · Answer 2 · 2018-04-09T19:01:34.613

0

InStrRev is going to be your friend here.

dim p as long, tmp as string, i as long

with worksheets("sheet1")
    for i=1 to .cells(.rows.count, "A").end(xlup).row
        tmp = .cells(i, "A").value2
        p = instrrev(tmp & chr(32), chr(32), 40)
        .cells(i, "B") = left(tmp, p-1)
        if p < len(tmp) then .cells(i, "C") = mid(tmp, p+1)
    next i
end with

edited Apr 09 '18 at 19:01

answered Apr 09 '18 at 18:33

This would work perfectly!! Although I realized I have some lines that are around 30-37 characters. Because they do not reach the 40 character they error out. How can I change that? – Janine Ortiz Apr 09 '18 at 18:57
Add an If statement that compares p to len(tmp). Might also want to 'seed' the original string with a trailing space. – Apr 09 '18 at 18:59

VBA/Excel: Split column into 40 characters and 800 characters, Whole Word

2 Answers2