Extracting Letters from Text

[wbsman]

Take three Words separated by spaces. I want the first letter after the space from each word and insert into a cell.

 

[philwojo]

I'm sure there are better ways to do this, but try this:

=CONCATENATE(LEFT(A1,1),(MID(A1,FIND(" ",A1)+1,1)),MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1))

That assumes your data starts in cell A1, update as needed.

 

[wbsman]

You are Brilliant, almost there. I want to take this string

(Dbl. Dutch Door 1) or any string and extract the letters after each space.

<tbody>
</tbody>

 

[philwojo]

OK, so I plugged that in to my test for my formula and I get DDD as a result, is that now what you want or what you are expecting?

If it isn't what you want, can you tell us what it should be then?

 

[JoeMo]

Here's a UDF you can try. Not clear to me if you want the first letter of the first word or not. If you want it just add a space before the first word.

Excel Workbook
	A	B
1	Dbl. Dutch Door 1	DD1
2	Aword Bword Cword Dword	BCD
Sheet5

Function ExtractLettersAfterSpaces(S As String) As String
Dim V, i As Long
V = Split(S, " ")
For i = LBound(V) + 1 To UBound(V)
    ExtractLettersAfterSpaces = ExtractLettersAfterSpaces & Left(V(i), 1)
Next i
End Function

 

[wbsman]

=ExtractLettersAfterSpaces(A1) This is what i want, however it returns the value of (#Name?)

 

[wbsman]

OK, so I plugged that in to my test for my formula and I get DDD as a result, is that now what you want or what you are expecting?

If it isn't what you want, can you tell us what it should be then?

I'm wanting DDD1 from that to create item numbers.

 

[philwojo]

Did you copy and paste the vba code into your workbook? If not the function won't be know to excel.

And you never answered what you expected the output to be for your example.

 

[wbsman]

i'm wanting DDD1 from Double Dutch Door 1

 

[JoeMo]

=ExtractLettersAfterSpaces(A1) This is what i want, however it returns the value of (#Name?)

That's because you didn't install the function correctly.

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.