• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1562
  • Last Modified:

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

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.
0
immixGroup
Asked:
immixGroup
1 Solution
 
YveauCommented:
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 ...
0
 
YveauCommented:
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 ?

0
 
AkisCCommented:
       Dim xStr As String = returnFirstLettersOfString("The Dog")
        MsgBox(xStr)

    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) & "-"
        Next
        If retVal.EndsWith("-") Then
            retVal = retVal.Remove(retVal.Length - 1, 1)
        End If
        Return retVal
    End Function
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Bob LearnedCommented:
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)
            wordList.Add(match.Value)
        Next match

        Return wordList

    End Function

End Class

Bob
0
 
immixGroupAuthor Commented:
Thanks Yveau. Sorry to the rest who replied, I tried the first response and it did what I needed.

Thanks!
0
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now