splitting cells capital letters

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Splitting Cells by Capital Letters

Splitting Cells by Capital Letters

resolvedResolved · 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...

Splitting data in cells with capital letters.xls

RE: Splitting Cells by Capital Letters

Thanks Rajeev!

Life saver!


 

Excel tip:

Conditional Formatting in Excel 2010

If 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:

1) Select the data you wish to apply the format to and click Conditional Formatting
2) A list of options will then appear, from this list, choose the format you wish to display e.g. find all cells with a value less than 0
3) Excel will then highlight all of these cells

To remove this: select the highlighted cells, click the drop down on the Conditional Formatting icon and select Clear Rules from selected cells.

View all Excel hints and tips


Server loaded in 0.08 secs.