Wednesday, August 09, 2006

Choosing the first item from a comma separated list in an Excel Cell

Excel spread sheet cells have text items separated by commas as in:
A1: abcd,efgh,iu0

These entries may be due to bad entry practices.
It is required to clean entry to retain only one item, the first
item in the comma separated list which may be pasted into another cell.

This VBA code picks the first item from a comma separated list
in cell A10.
'---------------
Sub combine()
Dim rng As Range
Set rng = Range("A10")
Dim strg As String
strg = rng.Value
Dim txt As String
txt = fsrch(strg)
Dim txt1 As String
txt1 = fitem(txt, strg)
'MsgBox (txt1)
ActiveCell.Value=txt1
End Sub
'----
Function fsrch(x) As String
fsrch = InStr(1, x, ",")
End Function
'----
Function fitem(x1, x2) As String
fitem = Left(x2, x1 - 1)
End Function

No comments:

DMCA.com Protection Status