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

questforexcel

Board Regular
Joined
Jan 18, 2019
Messages
128
Office Version
  1. 2013
Platform
  1. Windows
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,
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
ABC
1This is four wordsTIFWTIFW
2This for ThreeTFT TFT
3Just TwoJT JT
4OneOO
5  
6This is 6/03/2023This is 6/03/2023This is 6/03/2023
7123 Hello123 Hello123 Hello
8Here are six words to testHASWTT
96/03/20236/03/20236/03/2023
10123123123
Sheet1
Cell Formulas
RangeFormula
B1:B7,B9:B10B1=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:C10C1=LorN(A1)


The code for the LorN UDF:

VBA Code:
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
 
Upvote 0
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.

VBA Code:
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
AB
1This Is Four WordsTIFW
2This For ThreeTFT
3Just TwoJT
4OneO
5 
6This Is 6/03/2023This Is 6/03/2023
7123 Hello123 Hello
8Here Are Six Words To TestHASWTT
96/03/20236/03/2023
10123123
Sheet1
Cell Formulas
RangeFormula
B1:B10B1=Initials(A1)
 
Last edited:
Upvote 0
Same for me. This is a another UDF to consider. No looping involved with this one.

VBA Code:
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
AB
1This Is Four WordsTIFW
2This For ThreeTFT
3Just TwoJT
4OneO
5 
6This Is 6/03/2023This Is 6/03/2023
7123 Hello123 Hello
8Here Are Six Words To TestHASWTT
96/03/20236/03/2023
10123123
Sheet1
Cell Formulas
RangeFormula
B1:B10B1=Initials(A1)
Thank you very much for the kind help and effort. Much appreciated.
 
Upvote 0
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
ABC
1This is four wordsTIFWTIFW
2This for ThreeTFT TFT
3Just TwoJT JT
4OneOO
5  
6This is 6/03/2023This is 6/03/2023This is 6/03/2023
7123 Hello123 Hello123 Hello
8Here are six words to testHASWTT
96/03/20236/03/20236/03/2023
10123123123
Sheet1
Cell Formulas
RangeFormula
B1:B7,B9:B10B1=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:C10C1=LorN(A1)


The code for the LorN UDF:

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top