RaviWildcat
Board Regular
- Joined
- Jun 18, 2010
- Messages
- 124
- Office Version
- 365
- Platform
- Windows
- MacOS
Hello everyone - I've got a cell with several words in it and I want to capitalize only the names:
So, if I have a list with 3 names
Greg
Peter
Bobby
and my value in cell A1 is
greg -play the guitar for peter and bobby
I can probably do something like
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"greg","Greg"),"peter","Peter"),"bobby","Bobby")
This will work if I have 3 names but could get cumbersome if I have hundreds of names to check.
Is there a way to specify how many times to loop in a cell?
(I could also do an index match table to store all the names, but I'd still need to first check for name 1, then name 2 then name 3 etc)
I can also write a macro to put every word into an index table and then compare that table against a match table etc. but I wonder if it's possible to solve using a formula?
Ravi
So, if I have a list with 3 names
Greg
Peter
Bobby
and my value in cell A1 is
greg -play the guitar for peter and bobby
I can probably do something like
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"greg","Greg"),"peter","Peter"),"bobby","Bobby")
This will work if I have 3 names but could get cumbersome if I have hundreds of names to check.
Is there a way to specify how many times to loop in a cell?
(I could also do an index match table to store all the names, but I'd still need to first check for name 1, then name 2 then name 3 etc)
I can also write a macro to put every word into an index table and then compare that table against a match table etc. but I wonder if it's possible to solve using a formula?
Ravi