I need to split these three words that are currently separated by the hash (#) symbol. I need to write a sub procedure (Figure 1that extracts the words into three separate columns, which must be linked by the button “Click Here”. I don’t know how to link a user defined function to the button , and I don’t know how to separate words , only letters.
User Defined Function - Splitting Words using Symbols
- chemistrygeek
- Thread is marked as Resolved.
-
-
-
Attach an example workbook, we cannot work with images.
-
Sorry - see attached doc and code that Ive done so far
-
Use the split procedure.
Display MoreCode- Private Sub btnClickHere_Click()
- Dim rng As Range, c As Range
- Dim i As Integer
- Dim Orig As Variant
- Dim txt As String
- Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
- For Each c In rng.Cells
- txt = c.Value
- Orig = Split(txt, "#")
- For i = 0 To UBound(Orig)
- Cells(c.Row, i + 2) = Orig(i)
- Next i
- Next c
- End Sub
-
Your code is a sub so it requires End Sub not End Function.
The declaration at the top should be Option Explicit not Options Explicit
Is this what you want? If so you can do it with Text To Columns in the Data tb of the Ribbon