Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: concatenate

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    is there any way to concatenate the first letter of 5 or so words in the same cell, in order to produce a code. e.g if i had in cell A1 "the mark tom and travis show" i would want the formula to produce "tmtats"

    Please help me, as this is a vital part to my project

    BoB

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you don't mind using VBA, then try the following:

    Sub FirstLettersOfString()
    Dim FirstLetters(100)
    Dim counter As Integer
    Dim YourString As String

    FirstLetters(0) = Mid(ActiveCell, 1, 1)
    CellLength = Len(ActiveCell)

    For i = 1 To CellLength
    If Mid(ActiveCell, i, 1) = " " Then
    counter = counter + 1
    FirstLetters(counter) = Mid(ActiveCell, i + 1, 1)
    End If
    Next
    For h = 0 To counter
    YourString = YourString & FirstLetters(h)
    Next

    MsgBox YourString
    End Sub
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •