Find First Letter in the word

[MohitMathur1234]

Hi All,

I have some data where I need to find First Letter of the word and I have two columns but there are some condition in this.
1. I don't need repeat value,
2. 'And' or '&' will be not come.

For more Data is attached with example output.
Please advise.

 

[vletm]

@MohitMathur1234
Something like this ...
ps. I marked few cells in D-column with red

 

[MohitMathur1234]

Thanks for the reply BUT..!! it's not working.. Means
If you go to the Row no. 30 It's showing wrong.Capital D (of Data) is hiding D for Director.
Same as for Row no. 32 Strategy (S) is hiding for Senior(S).

Please help..

 

[vletm]

MohitMathur1234
Do You use same logic like with 8 & 21 or what?

You wrote:
I need to find First Letter of the word & I don't need repeat value

30: Data Strategy | Group Director Data Strategy => DSG (DDS)
32: Data Strategy | Senior Manager Data Strategy=> DSM (SDS)
Marked and Non BOLDED letter are repeated or how?
You could also sort those bolded First Letters if it would help.

 

[shrivallabha]

You can use formula also to arrive at this. Your data format is bit inconsistent. e.g. Cell A20 has correct replacement pattern in B20 but that cannot be said for A21.

In cell E2:
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"and",""),"&",""))&" "&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B2,"and",""),"&","")),TRIM(SUBSTITUTE(SUBSTITUTE(A2,"and",""),"&","")),""),"and",""),"&","")

In cell F2:
=CONCATENATE(LEFT(TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),1,99))),LEFT(TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),1*99,99))),LEFT(TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),2*99,99))),LEFT(TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),3*99,99))),LEFT(TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),4*99,99))),LEFT(TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),5*99,99))),LEFT(TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),6*99,99))),LEFT(TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),7*99,99))),LEFT(TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),8*99,99))),LEFT(TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),9*99,99))))
Second formula is long but it is just concatenation of the same logic for joining upto 10 word initials.

Copy down to the last row.

 

[Nebu]

Hi:

Try the following code:

Sub test()
Application.ScreenUpdating = False

Dim myArray As Variant
Dim col As New Collection, y

For i& = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    str1$ = Sheet1.Range("A" & i) & " " & Range("B" & i)
    myArray = Split(str1, " ")

        For Each y In myArray
            On Error Resume Next
            If y <> "and" And y <> "&" Then col.Add y, y
        Next

        For j& = 1 To col.Count
            Cells(i, 3) = Cells(i, 3) & Left(col(j), 1)
        Next

Set col = Nothing
Next

Application.ScreenUpdating = True
End Sub

Thanks