Splitting data can be a cumbersome and highly manual task, and could result in inaccurate data. Splitting data, moving it about and intelligently preparing large amounts of extracted data can be accomplished in seconds using a datanology VBA Solution.
In the short example below (data sanitised under DPA) the extracted data which was originally 12,000 names and email addresses was manipulated into a single row, and the forename and surname associated with the email was split from its single string into a structured ‘First Name’, ‘Surname’ before being linked back to it’s associated email address. The data was structured such that the persons name was listed, a blank row, their email address and then the next persons name without any blank row. The solution created allowed for this logic to be followed and obtain the accurate and quick result.
The VBA Solution can be carried out over and over again, obtaining thousands of results within seconds, and removing the scope for errors and wasted time cutting, pasting etc.
This VBA Code appears in several snippets, including a UDF to identify the First name, and a UDF to identify the Surname. The entire code needs to be pasted into the VB Editor (ALT+F11) but can be amended as you see fit to adapt to your own dataset.
Function GETFIRSTWORD(Text As String, Optional Separator As Variant)
Dim firstword As String
If IsMissing(Separator) Then
Separator = " "
firstword = Left(Text, InStr(1, Text, Separator, vbTextCompare))
GETFIRSTWORD = Replace(firstword, Separator, "")
Function ReturnLastWord(The_Text As String)
Dim stGotIt As String
stGotIt = StrReverse(The_Text)
stGotIt = Left(stGotIt, InStr(1, stGotIt, " ", vbTextCompare))
ReturnLastWord = StrReverse(Trim(stGotIt))
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
OTP = 2
'find the first row that is not blank and is not an email address
For n = 1 To lastrow
x = ActiveSheet.Range("A" & n)
y = InStr(1, x, "@")
If x <> "" And y = 0 Then
'Move first word to col A, last word to col B and email that is 2 cells below to col C
Worksheets("Output").Range("A" & OTP) = GETFIRSTWORD(ActiveSheet.Range("A" & n))
Worksheets("Output").Range("B" & OTP) = ReturnLastWord(ActiveSheet.Range("A" & n))
Worksheets("Output").Range("C" & OTP) = ActiveSheet.Range("A" & n + 2)
OTP = OTP + 1
If you’d like to discuss data splitting, moving and manipulation of any sort please feel free to leave us a message below or contact us through the form below. We could transform your business data process in as little as 24hrs, sometimes quicker.