A formula to pick up the first letter of words in a cell

[questforexcel]

Hi,

I have two/three or 4 words in cells in my excel file across columns.

I need a formula to pick up the first letter of the word in excel. These words are separated by a space.

There are some cases where there are more words. How do I setup a formula to pick up values after every space or for every new word in that cell? Also each new word is written in proper format, like the the starting letter of the word is in Caps.

I would like the formula, if possible, to check if there are any numeric values in the field, like dates. IF so I would like to not just pick up the first letter but the entire cell value.

How can this be done. I was currently using this formula. =TRIM(LEFT(G13,1)&MID(G13,FIND(" ",G13)*1,2))&TRIM(LEFT(G14,1)&MID(G14,FIND(" ",G14)*1,2))

But had two issues.

1) It generated a space on the second word in the same cell. Meaning if the word was Current Month. The formula generated a space and then M i.e. " M"
2) For Numeric date fields it gave me a #VALUE error due to which, i think my pivot data field cannot be grouped right at the moment.

Could you please guide and help.

Thank you,

 

[kevin9999]

You could use native functions (I note you have 2013 on your profile) but it will start to get messy if you have more than 4 words. Personally, I would use a UDF which could accommodate as many words as you're likely to have. Two options offered below (I'm sure there must be a more elegant solution to the native formula version).

Book1
	A	B	C
1	This is four words	TIFW	TIFW
2	This for Three	TFT	TFT
3	Just Two	JT	JT
4	One	O	O
5
6	This is 6/03/2023	This is 6/03/2023	This is 6/03/2023
7	123 Hello	123 Hello	123 Hello
8	Here are six words to test		HASWTT
9	6/03/2023	6/03/2023	6/03/2023
10	123	123	123
Sheet1

Cell Formulas
Range	Formula
B1:B7,B9:B10	B1	=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0,A1,UPPER(LEFT(A1)&MID(A1,FIND(" ",A1&" ")+1,1)&MID(A1,FIND(" ",A1&" ",FIND(" ",A1&" ")+1)+1,1)&MID(A1&" ",FIND(" ",A1&" ",FIND(" ",A1&" ",FIND(" ",A1&" ")+1)+1)+1,1)))
C1:C10	C1	=LorN(A1)

The code for the LorN UDF:

Function LorN(r As Range) As String
    Application.Volatile
    Dim ar, i As Long, j As Long
    For i = 1 To Len(r)
        If IsNumeric(Mid(r, i, 1)) Then j = j + 1
    Next i
        If j > 0 Then
            LorN = r
        Else
            ar = Split(r, " ")
            For i = LBound(ar) To UBound(ar)
                LorN = LorN & Left(ar(i), 1)
            Next i
            LorN = UCase(LorN)
        End If
End Function

 

[Peter_SSs]

Personally, I would use a UDF which could accommodate as many words as you're likely to have.

Same for me. This is a another UDF to consider. No looping involved with this one.

Function Initials(s As Variant) As Variant
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d"
    If .Test(s) Then
      Initials = s
    Else
      .Pattern = "([A-Z])([^ ]*)( |$)"
      Initials = .Replace(s, "$1")
    End If
  End With
End Function

questforexcel.xlsm
	A	B
1	This Is Four Words	TIFW
2	This For Three	TFT
3	Just Two	JT
4	One	O
5
6	This Is 6/03/2023	This Is 6/03/2023
7	123 Hello	123 Hello
8	Here Are Six Words To Test	HASWTT
9	6/03/2023	6/03/2023
10	123	123
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=Initials(A1)

 

[questforexcel]

Same for me. This is a another UDF to consider. No looping involved with this one.

Function Initials(s As Variant) As Variant
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d"
    If .Test(s) Then
      Initials = s
    Else
      .Pattern = "([A-Z])([^ ]*)( |$)"
      Initials = .Replace(s, "$1")
    End If
  End With
End Function

questforexcel.xlsm
	A	B
1	This Is Four Words	TIFW
2	This For Three	TFT
3	Just Two	JT
4	One	O
5
6	This Is 6/03/2023	This Is 6/03/2023
7	123 Hello	123 Hello
8	Here Are Six Words To Test	HASWTT
9	6/03/2023	6/03/2023
10	123	123
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=Initials(A1)

Thank you very much for the kind help and effort. Much appreciated.

 

[questforexcel]

You could use native functions (I note you have 2013 on your profile) but it will start to get messy if you have more than 4 words. Personally, I would use a UDF which could accommodate as many words as you're likely to have. Two options offered below (I'm sure there must be a more elegant solution to the native formula version).

Book1
	A	B	C
1	This is four words	TIFW	TIFW
2	This for Three	TFT	TFT
3	Just Two	JT	JT
4	One	O	O
5
6	This is 6/03/2023	This is 6/03/2023	This is 6/03/2023
7	123 Hello	123 Hello	123 Hello
8	Here are six words to test		HASWTT
9	6/03/2023	6/03/2023	6/03/2023
10	123	123	123
Sheet1

Cell Formulas
Range	Formula
B1:B7,B9:B10	B1	=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0,A1,UPPER(LEFT(A1)&MID(A1,FIND(" ",A1&" ")+1,1)&MID(A1,FIND(" ",A1&" ",FIND(" ",A1&" ")+1)+1,1)&MID(A1&" ",FIND(" ",A1&" ",FIND(" ",A1&" ",FIND(" ",A1&" ")+1)+1)+1,1)))
C1:C10	C1	=LorN(A1)

The code for the LorN UDF:

Function LorN(r As Range) As String
    Application.Volatile
    Dim ar, i As Long, j As Long
    For i = 1 To Len(r)
        If IsNumeric(Mid(r, i, 1)) Then j = j + 1
    Next i
        If j > 0 Then
            LorN = r
        Else
            ar = Split(r, " ")
            For i = LBound(ar) To UBound(ar)
                LorN = LorN & Left(ar(i), 1)
            Next i
            LorN = UCase(LorN)
        End If
End Function

Thank you for your kind help.

 

[kevin9999]

Glad we could help, and thanks for the feedback