Extract First Letters of each word in a String with VB, and then move onto the next string- Finally combine all letters with hyphens

Posted on 2007-10-11
Last Modified: 2010-05-18
I want to figure out a way using VB to extract the first letter in each individual word of a few different strings, and then combine them with hyphens in between each one. Basically, I'm trying to create unique identifiers for short sentences, that I can repeat based on the same sentence in the future.

for example

If i have 3 strings that are on the same row in separate columns (1-2-3), and then
1.) "The Dog"
2.) "Ran Across"
3.) "The Yard"

I'd like for the end result of be "TD-RA-TY"

Another variable that will only exist on certain rows will be a numeric with a metric (example: 40YD) that i'd like to append to the end (TD-RA-TY-40YD). So there would have to be some sort of exception that said basically IF the 4th column is blank, do nothing, if not, insert a hyphen along with the entirety of the 4th column.

This will be repeated for a few hundred rows of excel data. Excel formula's aren't advanced enough to do this, and Vb is the only thing I can think of that might be able to help. I can also work with MS SQL server 2005 & Access.
Question by:immixGroup
    LVL 18

    Expert Comment

    Here is the solution in SQL"

    create table #Y (col1 varchar(20), col2 varchar(20), col3 varchar(20), col4 varchar(20) null)

    insert into #Y values ('The Dog', 'Ran Across', 'The Yard', '40YD')
    insert into #Y values ('This Is', 'A Very', 'Simple Example', NULL)
    insert into #Y values ('What About', 'This Last', 'Tiny Example', NULL)

    select *
    ,      left(Col1,1) + substring(Col1, charindex(' ', col1) + 1, 1)
    +      '-'
    +      left(Col2,1) + substring(Col2, charindex(' ', col2) + 1, 1)
    +      '-'
    +      left(Col3,1) + substring(Col3, charindex(' ', col3) + 1, 1)
    +      isnull('-' + col4, '')
    from   #Y

    Hope this helps ...
    LVL 18

    Accepted Solution

    Sorry, forgot the result:

    col1                 col2                 col3                 col4                
    -------------------- -------------------- -------------------- -------------------- -----------------------------
    The Dog              Ran Across           The Yard             40YD                 TD-RA-TY-40YD
    This Is              A Very               Simple Example       NULL                 TI-AV-SE
    What About           This Last            Tiny Example         NULL                 WA-TL-TE

    The last column is what you are looking for, right ?

    LVL 11

    Expert Comment

           Dim xStr As String = returnFirstLettersOfString("The Dog")

        Public Function returnFirstLettersOfString(ByVal thisString) As String
            Dim retVal As String = ""
            Dim splitArr As String() = Split(thisString, " ")
            For i As Integer = 0 To UBound(splitArr)
                retVal &= Mid(splitArr(i), 1, 1) & "-"
            If retVal.EndsWith("-") Then
                retVal = retVal.Remove(retVal.Length - 1, 1)
            End If
            Return retVal
        End Function
    LVL 96

    Expert Comment

    by:Bob Learned
    You could use a regular expression parser to get the words, and then use Substring to get the first letters for each word in a sentence.

    Here is a VB.NET 2005 routine to extract words from a string:

    Imports System.Collections.Generic
    Imports System.Text.RegularExpressions

    Public Class RegexParser

        Public Shared Function GetWords(ByVal input As String) As List(Of String)

            Dim pattern As String = "[\w]+"

            Dim wordList As New List(Of String)

            For Each match As Match In Regex.Matches(input, pattern)
            Next match

            Return wordList

        End Function

    End Class


    Author Comment

    Thanks Yveau. Sorry to the rest who replied, I tried the first response and it did what I needed.

    LVL 18

    Expert Comment

    Glad I could be of any help and thanks for the grade !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now