VBA: Take first word from string (first word is seperated by a comma) and populate on different worksheet

[Foster]

Hello All,

I am needing to to take the first word of a string, each word in the string is seperated by a comma, and populate that first word into another column on a different worksheet using VBA.


Source Sheet;
"packagingData"

Destination Sheet;
"Template"

The logic of what I need the macro to do is as follows;

Identify which cells need to be populated by the first word in column 'SpecDesCol' on whether the column 'Spec Type' contains A certain Z code (ZPC_RM_CTN) on workshet "packagingData" if the 'Spec Type' column contains that Z code then populate the 'CategoryCol' column on worksheet "Template" with the first word of the 'SpecDesCol' (on worksheet "packagingData")that contains the string.

here is what I what I have so far;

Sub PopulateTemplate()
'Check to see if there is anything in the next row
Dim r As Long
Dim RowN As Long
Dim CategoryCol, SpecDesCol, As Integer

Set WB = ActiveWorkbook
r = 2
RowN = 2
CategoryCol = FindColNum("Template", "Category")
SpecTypeCol = FindColNum("packagingData", "Spec Type")
SpecDesCol = FindColNum("packagingData", "Spec Desc")

'Loop until the sheet is out of data

Do Until WB.Sheets("packagingData").Cells(r, 1).Value = ""
'Map to proper now name based on information on the mapping sheet
'Bottle/Jar/Jug

If WB.Sheets("packagingData").Cells(r, SpecTypeCol).Value = "ZPC_RM_BTL" Then (populate CategoryCol with the first word from SpecDesCol)

Reminder: SpecTypeCol & SpecDesCol are on worksheet "packagingData"
CategoryCol is on "Template" worksheet"

Thank you for any help on this!