Basics of VBA / Macros Chapter – 40 (String Manipulation Part‑2)

⚡ Macro Developer Toolkit

Copy or Export this Code to VBA

Use these verified developer utilities to run this lesson directly inside your Microsoft Excel Visual Basic Editor.

🖇 View Code Example
⏱ 2 min readUpdated 12 November 2026

Separate Strings

Below we will look at a program in Excel VBA that separates strings.

Situation:

Separate Strings in Excel VBA

Place a command button on your worksheet and add the following code lines:

We declare a variable called fullname of type String, a variable called commaposition of type Integer, and a variable called i of type Integer.

Advertisement
Dim fullname As String, commaposition As Integer, i As Integer

The problem we are dealing with is that we need to tell Excel VBA where we want to separate the string. In case of Smith, Mike the comma is at position 6 while in case of Williams, Janet the comma is at position 9.

We use a loop to execute the operations on each name entered in Excel. First, we initialise the variable fullname. Next, we use the Instr function to find the position of the comma.

For i = 2 To 7
    fullname = Cells(i, 1).Value
    commaposition = InStr(fullname, ",")

We want to write the part after the comma to column B and the part in front of the comma to column C. You can achieve this by adding the lines:

Cells(i, 2).Value = Mid(fullname, commaposition + 2)
Cells(i, 3).Value = Left(fullname, commaposition - 1)

Mid(fullname, commaposition + 2) means we want the part of fullname starting at character ‘commaposition + 2’ (this is exactly the first name).

Left(fullname, commaposition – 1) means we want the part of fullname starting at the beginning until character ‘commaposition- 1’ (this is exactly the last name).

Advertisement

Don’t forget to close the loop.

Next i

Add six names separated by a comma and space to Range(“A2:A7”). Test the program.

Result:

Separate Strings Result

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong