Extract letters

[Yippy_Kiyey]

Hello,

Excel Workbook
	A	B	C	D
1	Company	1st	2nd	3rd
2	American Express	A	E
3	Best Buy	B	B
4	Amazon	A
5	March of Dimes	M	O	D
Sheet1

 

[SteveO59L]

No question !

 

[Yippy_Kiyey]

Hello, Sorry, I click submit a little too soon. The thing is I need a formula to put on column C & D that will enter the 1st letter of the company's second name on col C and also the 1st letter of the third name on col D.
I can do col B, for the 1st name using the left function.

Thank you

Rey

 

[Scott Huish]

Put all this in a standard module and run the macro.

Function GetFirstLetters(r As String) As Object
Dim m As Object, s As String
r = " " & Trim(r)
With CreateObject("vbscript.regexp")
    .Pattern = " [A-Z]"
    .Global = True
    If .test(r) Then Set m = .Execute(r)
End With
Set GetFirstLetters = m
End Function
 
Sub SplitLetters()
Dim c As Range, t As Object, d, x As Long
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Set t = GetFirstLetters(c.Text)
    If t.Count Then
        For Each d In t
            x = x + 1
            c.Offset(, x) = Trim(d)
        Next
    End If
    x = 0
Next
End Sub

 

[Yippy_Kiyey]

Thank you for the reply. Does it mean that it can not be done thru a cell formula?

 

[Rick Rothstein]

Put this formula in C2....

=UPPER(MID(A2,FIND(" ",A2&" ")+1,1))

and this formula in D2...

=UPPER(MID(A2,FIND(" ",A2&" ",FIND(" ",A2&" ")+1)+1,1))

and copy down as needed.

 

[njimack]

Formula approach (bit messy):
B2: =LEFT(A2)
C2: =IF(LEN($A2)-LEN(SUBSTITUTE($A2," ",""))>=LEFT(C$1)-1,MID($A2,FIND(" ",$A2)+1,1),"")
D2: =IF(LEN($A2)-LEN(SUBSTITUTE($A2," ",""))>=LEFT(D$1)-1,MID($A2,FIND(" ",$A2,FIND(" ",$A2)+1)+1,1),"")

 

[T. Valko]

Thank you for the reply. Does it mean that it can not be done thru a cell formula?

Try this...

Book1
	A	B	C	D
1	Company	1st	2nd	3rd
2	American Express	A	E	_
3	Best Buy	B	B	_
4	Amazon	A	_	_
5	March of Dimes	M	o	D
Sheet1

This formula entered in B2:

=LEFT(TRIM(MID(SUBSTITUTE(" "&$A2&" "," ",REPT(" ",255)),COLUMNS($B2:B2)*255,255)))

Copy down to B5 then across as needed (until you get a full column of blanks).

 

[Yippy_Kiyey]

Thank you very much guys. You guys are great. I appreciate all the help.

So long,

Rey

 

[T. Valko]

Thank you very much guys. You guys are great. I appreciate all the help.

So long,

Rey

You're welcome. We appreciate the feedback!