How to customize a ancromne in excel 2

[Sahubba]

Hello,
I am having a hard time finding a code to only give me the first letter of the first 3 words even if there is more then 3 words and to include this - as a space. e.i.

Consult-Archive LV = CAL
Ambulance Rec-scanned LV = ARS
eDetailed Pt Charting LV = ePC


Here is the code that I am currentyly using

Function Ext(myText As String) As String
Dim i As Integer, myWord As String
myWord = Left(myText, 1)
For i = 2 To Len(myText)
If Mid(myText, i, 1) = " " Then
myWord = myWord & Mid(myText, i + 1, 1)
End If
Next i
Ext = myWord
End Function


and the results I am getting is this

Consult-Archive LV = CL
Ambulance Rec-scanned LV = ARL
eDetailed Pt Charting LV = ePCL



Thanks For your Help

 

[SkipVought]

hi,

Function Ext(myText As String) As String
    Dim i As Integer, a
    
    a = Split(myText, " ")
    
    For i = 0 To UBound(a)
        Ext = Ext & Left(a(i), 1)
    Next i
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

How about this

Function Ext(myText As String) As String
    Dim i As Integer, a, b, j As Integer, k As Integer
    
    a = Split(myText, " ")
    
    For i = 0 To UBound(a)
        b = Split(a(i), "-")
            
        For j = 0 To UBound(b)
            Ext = Ext & Left(b(j), 1)
            If k > 3 Then Exit For
            k = k + 1
        Next
        If k > 3 Then Exit For
        k = k + 1
    Next i
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

A starting point:

Function Ext(myText As String) As String
Dim i As Integer, myWord As String, l As Integer
myWord = Left(myText, 1)
l = 1
For i = 2 To Len(myText)
    If Mid(myText, i, 1) = " " Or Mid(myText, i, 1) = "-" Then
        l = l + 1: i = i + 1
        myWord = myWord & Mid(myText, i, 1)
        If l = 3 Then Exit For
    End If
Next i
Ext = myWord
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Sahubba]

Thank you for your help!
But it didnt work.
it gave me a value of 0.

In column A list all of the names and in column B is where I want to enter =EXT(A1) to display the acronyms

 

[PHV]

Did you try my suggestion ?

 

[SkipVought]

Where do you have your code stored? You can see the location in the VB Editor F4 Properties Window.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Sahubba]

I am sorry I didnt refreash my browser so I didnt see you other post.
I just saw your first code.

Your last code worked great! Thank you so much!
I have another question too, is there a way I could add if there is only 1 word then to take the 3 letters from that word too?
I only thought I had an issue with too many letters and now I have a issue with not enough. I need to have exactly 3 letters.
E.I.

EKG LV = EKG
EXCLUDE = EXC
Facesheet LV = FAC


ETC.

Thank you for your help

 

[PHV]

Like this ?

Function Ext(myText As String) As String
Dim i As Integer, myWord As String, l As Integer
myWord = Left(myText, 1)
l = 1
For i = 2 To Len(myText)
    If Mid(myText, i, 1) = " " Or Mid(myText, i, 1) = "-" Then
        l = l + 1: i = i + 1
        myWord = myWord & Mid(myText, i, 1)
        If l = 3 Then Exit For
    End If
Next i
If l < 3 Then
  Ext = Left(myText, 3)
Else
  Ext = myWord
End If
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Sahubba]

Yes PHV!
Thank You so much!
Thank You too SkipVought!
Both you are AWESOME!