Spliting a block of cells in a column

Question

Just for rows 76 thru 81, can Column B get split so Calendar names can be separate from person's name?&nbsp;Row&nbsp; &nbsp;Column A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column C69&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;67&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Seattle WA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $4,66670&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;68&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Mansfield PA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$2,540&nbsp; &nbsp; &nbsp; &nbsp;rows 71-74 are empty75&nbsp; &nbsp; &nbsp; Calendars76&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Jim - Inspiration&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$&nbsp; 4477&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sally -&nbsp;78&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Xander - Procrastination&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $&nbsp; 1279&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Wolfy - Inspiration&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$&nbsp; 4480&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Aaron -&nbsp;81&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Archie - Determination&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $&nbsp; 688283&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TOTAL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $17884858687&nbsp;

martin_weiss · Answer

Hi&nbsp;MariluWho&nbsp;&nbsp;there are several ways to do this. Either statically with a one time approach:Select the values in B76:B81, then choose menu "Data | Text do Columns"Make sure "Delimited" is selected on click on "Next"Check "Other" checkbox and enter the - as a delimiter, then click on "Next":Enter the cell in the destination field, where the text should appear and click on "Finish":&nbsp;&nbsp;Alternatively, you can do it dynamically with formulas.To get the first name:=LEFT(B76;FIND("-";B76)-2)To get the calender name:=IFERROR(RIGHT(B76;LEN(B76)-FIND("-";B76)-1);"no calendar")&nbsp;

mariluwho · Answer

Thank you&nbsp;Martin_Weiss&nbsp;#1.&nbsp; I'm known to do things "dynamically" &amp; believe in formulas so I'll go with #2.#2.&nbsp; I'm disappointed.&nbsp; Now I have to create &amp; populate the columns for the names &amp; calendars.&nbsp; So much for Excel Magic.&nbsp; I expected you to have a magic formula that would do it for me, like waving some magic wand over the cell to do it automatically.&nbsp; &nbsp;SMILES, MariluWho#3.&nbsp; Here's how I expected it to happen:&nbsp; highlight the cells from B76 thru B88 and separate that selection into columns.&nbsp; It is a block of text that will not have formulas so why not allow me to table that section of cells into 2 columns?&nbsp; &nbsp;why not?&nbsp; ......&nbsp; cuz I'm just a "smidgeon" in this matter.&nbsp; (from the Scottish word smitch, "an insignificant person.")&nbsp;&nbsp;

mariluwho · Answer

Perhaps I'm not clear on this one requirement I'm trying to achieve. Column B is a table with two columns, 1 for name &amp; 1 for calendar. I don't want to have to look to any other columns on my spreadsheet to see this info.

Forum Discussion

Spliting a block of cells in a column

3 Replies

Resources