Excel Forumla

[KyleKnight]

Hey guys,

I found parts of the forumla on a thread on this forum. The problem is i need it to be modified slightly to skip a word which i will explain in a sec. Here is my formula:

<code>=UPPER(LEFT(A40153,1)&IF(ISERROR(FIND(" ",A40153)),"",LEFT(MID(A40153,FIND(" ",A40153)+1,LEN(A40153)),1))&IF(ISERROR(FIND(" ",MID(A40153,FIND(" ",A40153)+1,LEN(A40153)))+FIND(" ",A40153)),"",LEFT(MID(A40153,FIND(" ",MID(A40153,FIND(" ",A40153)+1,LEN(A40153)))+FIND(" ",A40153)+1,1)))&"-"&D40153&"-"&H40153&"-"&G40153

So what this formula does is take the letter from each word from a three word sentence e.g. Please help me, becomes PHM. Now I have a four word sentence and I need it to skip a word in there e.g. Fruit of the Loom needs to be FOL and not FOT. This document i am working in has 58000 records.

Let me know if this makes sense?
</code>

 

[Sandeep Warrier]

Hi

How about a UDF?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Fruit of the Loom</td><td style=";">FOL</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=Acronym(<font color="Blue">TRIM(<font color="Red">SUBSTITUTE(<font color="Green">A1,"the",""</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Function Acronym(sInp As String) As String
    Dim WordCount As Long
    Dim vSplit As Variant
    WordCount = Len(sInp) - Len(Replace$(sInp, " ", "")) + 1
    Select Case WordCount
        Case 1
            Acronym = UCase$(Left$(sInp, 1))
        Case 2
            Acronym = UCase$(Left$(sInp, 1) & Mid$(sInp, InStr(sInp, " ") + 1, 1))
        Case Else
            vSplit = Split(sInp, " ")
            Acronym = UCase$(Left$(vSplit(0), 1) & Left$(vSplit(1), 1) & Left$(vSplit(2), 1))
    End Select
End Function

 

[KyleKnight]

I am not sure what UDF is. Could you please explain. I am quite a novice with Excel. I am actually a web/graphic designer.

 

[Sandeep Warrier]

In your Excel workbook, press Alt+F11
In the new window that opens up, press Alt+I+M
Paste the code from my earlier post in the white area on the right.
Press Alt+Q to close the new window.

Now use the formula as I've shown (changing cell references as required)

 

[KyleKnight]

So i did what you suggested and followed your instructions. but when i replace my long forumla with: "=Acronym(TRIM(SUBSTITUTE(A40153,"the","")))"

It just makes a mess and pushes my cell out whilst showing the code in that cell. I didnt touch your Visual Basic code though, just copy pasted it.

 

[KyleKnight]

Your Formula does work. You are brilliant! Thanks alot buddy