98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Splitting Cells by Capital Letters
Splitting Cells by Capital Letters
Resolved · Urgent Priority · Version 2007
Paul has attended:
Excel Intermediate course
Excel Advanced course
Splitting Cells by Capital Letters
Hi,
Does anyone know if there is a way to split a cell by Capital letters?
For example, turning IceCream into Ice Cream (either Ice and Cream in different cells or by inserting a space) and then doing this for Cells with Multiple capital letters (e.g. StrawberryIceCream and so on).
I've been given the formula below but it doesnt seem to do anything:
=LEFT(A1,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)
Thanks!
RE: Splitting Cells by Capital Letters
Dear Paul
Lets assume on Cell A1 you have the word that needs to be split.
You were almost there!! On cell B1 you enter the following function but you need to make sure that you use CTRL+SHIFT +ENTER after entering the function:
=LEFT(A1,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)
This will only provide you "Ice" as the result on Cell B1.
Then you click on Cell C1 and use the function:
=REPLACE(A1,1,LEN(B1),"")
followed by CTRL +SHIFT +ENTER
This should give you the result as "Cream"
I hope this helps. I am attaching an excel file which should help you to understand the formula bit clearly. Please observe the functions in Column B and C. You can replace your exsiting data in column A and see if it works!!
I hope this helps.
Please mark this post as resolved if it has answered your query!!
Many thanks
Kindest Regards
Rajeev Rawat
MOS Master Instructor 2000/2003
MCAS Master Instructor 2007
Attached files...
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Conditional Formatting in Excel 2010If you have lots of data in a spreadsheet, you may find that it is easier to read if you highlight some of the values. This is Conditional Formatting and here's how to use it: |