Split and LEFT operation on comma-separated values in cell

[netarc]

Hoping to get some advice on processing a split and trim operation on a cell containing between 2-4 comma-separated values.

The cell contains days separated by commas, for instance along the lines of...

• Thursday, Friday
• Monday, Wednesday, Thursday
• Monday, Wednesday, Thursday, Friday

I need to translate this into a string containing just the first letter of each day, for instance...

• T/F
• M/W/R
• M/W/R/F

I've tried a couple of variations on the VBA split function, but not having any luck. Would appreciate any advice ... thanks!

 

[JazzSP8]

Hi

You can use the grabcaps UDF from this thread;

http://www.mrexcel.com/forum/showthread.php?t=230646

Then you can use that with SUBSTITUTE;

=SUBSTITUTE(grabcaps(A1),",","/")

Would that do your job?

 

[Jon von der Heyden]

Here's a complete UDF to do what you want:

Public Function GetFirstLetter(ByVal strText As String, _
                               Optional ByVal strDelimiter As String = ",") As String
    Dim varTemp As Variant, lngItem As Long
    varTemp = Split(strText, strDelimiter)
    
    For lngItem = LBound(varTemp) To UBound(varTemp)
        If Len(varTemp(lngItem)) Then
            varTemp(lngItem) = Left$(WorksheetFunction.Trim$(varTemp(lngItem)), 1)
        End If
    Next lngItem
    GetFirstLetter = Join$(varTemp, strDelimiter)
End Function

 

[Rick Rothstein]

The cell contains days separated by commas, for instance along the lines of...

• Thursday, Friday
• Monday, Wednesday, Thursday
• Monday, Wednesday, Thursday, Friday

I need to translate this into a string containing just the first letter of each day, for instance...

• T/F
• M/W/R
• M/W/R/F

How is R the first letter of a day name? I think I understand... it's the 4th letter of the other day starting with T... which, of course, raises the question of Saturday/Sunday? Can you post the day names and letters you want returned? Also, are you looking to change the cell contents in place, or do you want the single-letter string placed in another cell?

 

[Comfy]

Sub DaySplit()
Dim Days() As String
Dim strDays As String
Dim i As Long, j As Long

    For i = 3 To 5
        Days = Split(Cells(i, 1).Value, ",")
        For j = LBound(Days) To UBound(Days) - 1
            strDays = strDays & Left(Trim(Days(j)), 1) & "/"
        Next j
        strDays = strDays & Left(Trim(Days(UBound(Days))), 1)
        Cells(i, 1).Value = strDays
        strDays = ""
    Next i

End Sub

 

[netarc]

Thank you all, I'll try these out l8r today once I'm back in front of computer.

Re: the days inquiry ... Sat/Sun not necessary for me, but come to think of it, an output which would probably scale better would be the first two chars, eg "Mo/Tu/We/Th/Fr/Sa/Su" ... I should be Avle to mod the functions above to do such.

Oh, to answer the other query I need the output in a new cell.

Btw, what does UDF stand for? (F I'm guessing is function).

 

[Rick Rothstein]

Re: the days inquiry ... Sat/Sun not necessary for me, but come to think of it, an output which would probably scale better would be the first two chars, eg "Mo/Tu/We/Th/Fr/Sa/Su" ... I should be Avle to mod the functions above to do such.

Oh, to answer the other query I need the output in a new cell.

Btw, what does UDF stand for? (F I'm guessing is function).

UDF stands for User Defined Function... it is VB code that creates a function which can be used on a worksheet just like any of the built-in functions that Excel offers you. Here is a UDF to do what you asked for...

Function TwoLetterDays(S As String) As String
  Dim X As Long, Days() As String
  Days = Split(Replace(S, " ", ""), ",")
  For X = 0 To UBound(Days)
    Days(X) = Left(Days(X), 2)
  Next
  TwoLetterDays = Join(Days, ", ")
End Function

To install this code, just put in in a standard Module just like you would do for a macro. Once you have done than, then all you have to do on your worksheet is put this formula in the cell you want to output your result to...

=TwoLetterDays(A1)

A1 can be any cells reference, of course, and this formula can be copied down or across as needed.

 

[netarc]

Fantastic, thank you all so much!