ExcelFilter: I don't see an easy one-function way to get the initial letters of a text string in Excel. Is there such a beast?

September 29, 2004 2:49 PM

ExcelFilter: I don't see an easy one-function way to get the initial letters of a text string in Excel. Is there such a beast? [=Text(MI)]

I need to do this for the first N words in the string. Let's say I have cells at A1 and B1 that look like this:

[A Test Text String]
[Another testing string, with more words]

In this case, I'd like to get the values "ATTS" and "ATSW." All the cells I need to operate on will always hold only strings.

[=INTITALS(A1,4)] is the ideal case, but as I said, it seems to not exist. TYVM.

posted by mwhybark to computers & internet (8 answers total)

=LEFT(A1,5)
posted by skwm at 3:36 PM on September 29, 2004

whoops, ignore my answer.
posted by skwm at 3:36 PM on September 29, 2004

Nope, there isn't such a beast. To do this, you'll have to write a ( Visual Basic | Microsoft ) script.
posted by escher at 3:38 PM on September 29, 2004

It's possible to do in Excel, but the result is going to be one terribly nasty piece of code (Probably like 40 characters of typing per character you want to check).

Basically, embedded if statements checking if each letter is a space, and if so, going back one letter and outputting that. I'm certain it's possible. But I don't have excel anymore to demonstrate it. Sorry.
posted by shepd at 4:15 PM on September 29, 2004

I don't think there is any one function, but this does not require visual basic if the strings are of comparable length. I think it will require three steps. If "A Test Text String" is in A1, use row A to find all the spaces. Put "=FIND(" ", $A$1)" in B1 and "=FIND(" ",$A$1,B1+1)" in C1. From C1 you can just fill right to extend the process of locating spaces in the string as far as you need to.

In row B, grab the characters that follow the spaces. A2=LEFT(A1,1) B2=MID($A$1,B1+1,1), and again fill right as far as you need to.

Step 3 is to use =concatenate() to add up the bits in row B.

You will have to fill right far enough to accomodate the string with the most spaces, and then clean up your results by trimming off any trailing spaces in the output.
posted by shinnin at 4:21 PM on September 29, 2004

If it's always the same number of characters (4), you can use the following function in a single cell. Just repeat the pattern for more characters and replace all instances of "A4" with whatever cell you want to grab the caps from.

(yeah it's messy, but it works :)

=LEFT(A4,1) & MID(A4,SEARCH(" ",A4) + 1,1) & MID(A4,SEARCH(" ", A4, SEARCH(" ",A4)+1)+1,1) & MID(A4, SEARCH(" ", A4, SEARCH(" ", A4, SEARCH(" ", A4) + 1) + 1) + 1,1)
posted by freshgroundpepper at 4:32 PM on September 29, 2004

Holy cow!

I have also received non-member emails. One clearly requires posting:

---

FROM: Paresh Ghaghda  to mwhybark
Hello, I saw your question but am not a member. If you insert the following code into a VBA Module, you should find that the function INITIALS is now available as you described:

Function INITIALS(str, n)
    Dim x As Variant
    x = Split(str, " ")
    For i = 0 To n - 1
        INITIALS = INITIALS & UCase(Left(x(i), 1))
    Next i
End Function

HTH,
Paresh

---

Which would seem to be as perfect a thread closure as I have ever seen! Thanks to everyone, and Excel dev team members: please credit Paresh if an INITIAL text function crops up down the road.

Damn, AskMe rules!
posted by mwhybark at 5:57 PM on September 29, 2004

Also: Pepper, I thought I posted my deep appreciation earlier, but the phantom preview bug appears to have kilt it. To reiterate: I will try your solution forthwith.

I look forward to implementing Paresh's as well.
posted by mwhybark at 7:55 PM on September 29, 2004