We help IT Professionals succeed at work.

need formula to  count the number of letters in the 2nd word of excel value

107 Views
Last Modified: 2017-03-13
example

A J Schreiber

result = 1, for the letter J which is 2nd workd
Comment
Watch Question

Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
Try this formula, where cell A1 contains your text.

=LEN(TRIM(MID(A1,FIND("|", SUBSTITUTE(A1, " ", "|", 1)),FIND(" ", SUBSTITUTE(A1, " ", "|", 1))-FIND("|", SUBSTITUTE(A1, " ", "|", 1)))))
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Try this and change any nth word in (2-1):
=IF(A1="","",LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (2-1)*LEN(A1)+1, LEN(A1)))))

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.