Nov 30 2021 12:36 PM
Just for rows 76 thru 81, can Column B get split so Calendar names can be separate from person's name?
Row Column A Column B Column C
69 67 Seattle WA $4,666
70 68 Mansfield PA $2,540
rows 71-74 are empty
75 Calendars
76 1 Jim - Inspiration $ 44
77 2 Sally -
78 3 Xander - Procrastination $ 12
79 4 Wolfy - Inspiration $ 44
80 5 Aaron -
81 6 Archie - Determination $ 68
82
83 TOTAL $178
84
85
86
87
Dec 02 2021 12:22 AM
Hi @MariluWho
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":
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")
Dec 02 2021 11:48 AM
Thank you @Martin_Weiss
#1. I'm known to do things "dynamically" & believe in formulas so I'll go with #2.
#2. I'm disappointed. Now I have to create & populate the columns for the names & calendars. So much for Excel Magic. 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. SMILES, MariluWho
#3. Here's how I expected it to happen: highlight the cells from B76 thru B88 and separate that selection into columns. 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? why not? ...... cuz I'm just a "smidgeon" in this matter. (from the Scottish word smitch, "an insignificant person.")
Dec 02 2021 11:54 AM