Formula to create an ID based on words in a cell?

[JohnClifford]

Hi there.

I'm looking for a way to isolate all words in a cell so that I can create an ID based on them. I've found formulae for finding the total number of words, and one for finding a finite number of words, but the problem is I don't know ahead of time how many words there will be. I need to return the first four letters from the first word and the first letter of the second if there are two--but the first three letters from the first, the first letter of the second and the first from the third if there are three, and so on.

I'll give some examples as I'm sure that didn't explain what I need very well:

This piggy - needs to return THISP
This little piggy - needs to return THILP
This little piggy went - needs to return THLPW

Does anyone know if there's a way to do this with a formula? Thanks in advance for any help you can give.

 

[kidwispa]

removed - didn't read the entire question...

 

[JohnClifford]

That would work if I needed to split specific strings, but I also don't know ahead of time what the strings are going to be. Thanks, though.

 

[jasonb75]

Can you use add-ins or VBA?

I have a solution, but it needs MCONCAT from morefunc, a similar UDF such as Aconcat might work, but it might be more practical to write the whole thing as a UDF if you're going to use that option.

 

[JohnClifford]

I can use VBA; add-ins might be an issue since this is for work and we have pretty strict screening policies for third-party or external solutions.

 

[Sektor]

UDF
Set reference: Tools -> References -> Microsoft VBScript Regular Expressions 5.5

Function GetID(Str As String) As String

    ' Set reference: Tools -> References -> Microsoft VBScript Regular Expressions 5.5
    
    Dim i As Integer, iCount As Integer, s As String
    Dim re As New RegExp, mc As MatchCollection, m As Match

    Application.Volatile
    
    With re
        .Global = True
        .Pattern = "\b\w+\b"
    End With
    
    Set mc = re.Execute(Str)
    
    Select Case mc.Count
        Case 2: s = Mid$(mc(0), 1, 4) & Mid$(mc(1), 1, 1)
        Case 3: s = Mid$(mc(0), 1, 3) & Mid$(mc(1), 1, 1) & Mid$(mc(2), 1, 1)
        Case 4: s = Mid$(mc(0), 1, 2) & Mid$(mc(1), 1, 1) & Mid$(mc(2), 1, 1) & Mid$(mc(3), 1, 1)
    End Select
    
    GetID = UCase(s)

End Function

 

[JohnClifford]

Sektor, I think I'm in love. Thanks!

 

[Arcticwarrio]

What formulas do you have at the moment?

 

[Arcticwarrio]

nvm didnt see sektors post :P

 

[Comfy]

Question answered I know but thought I might as well post this:

Function Cell_ID(ByVal cell As Range)
On Error Resume Next
Dim cLoop As Long, i As Long, j As Long, IDstr As String
    cLoop = Len(cell.Value) - Len(WorksheetFunction.Substitute(cell.Value, " ", "")) + 1
    Select Case cLoop
        Case 1
            IDstr = cell.Value
        Case 2
            IDstr = Left(cell.Value, WorksheetFunction.Find(" ", cell.Value, 1) - 1)
        Case 3
            IDstr = Left(cell.Value, 3)
        Case Else
            IDstr = Left(cell.Value, 2)
    End Select
    j = 1
    For i = 0 To cLoop - 1
        IDstr = IDstr + Mid(cell.Value, WorksheetFunction.Find(" ", cell.Value, j) + 1, 1)
        j = WorksheetFunction.Find(" ", cell.Value, j) + 1
    Next i
Cell_ID = UCASE(IDstr)
End Function