TEXTBEFORE not working in the FILTER function

T

TheColonist

New Member
Joined
Jun 23, 2023
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I work for a school for the blind and visually impaired. Every 2 weeks we get a new schedule. The first figure is an example of the schedule table. Most of the instructors are blind. This is cumbersome to navigate with a screen reader.

I created another worksheet for the instructors, Figure 2. I use the following formula to list the students and the class they are attending.

The formula below returns the student's first name from the "First" column and the last three letters of the Class in P1 if the first 4 characters in P1 = the first 4 characters of the instructor's name, which is a column header, in Figure 2. It works great, but some instructors may only have 2 or 3 letters in their first name, such as Al, or Don. Right now, I add characters to make the formula work and distinguish instructors that may have the same first 4 characters.

=TEXTJOIN(", ",1,FILTER(AT_VISP[First]&TEXTAFTER(AT_VISP[P1],"-"),

LEFT(AT_VISP[P1],4)=LEFT(Table36[[#Headers],[Randy]],4),""))

I tried the following formula, but I get a #NA error. Obviously, TEXTAFTER works in the FILTER function, but I can't get TEXTBEFORE to work.

=TEXTJOIN(", ",1,FILTER(AT_VISP[First]&TEXTAFTER(AT_VISP[P1],"-"),

TEXTBEFORE(AT_VISP[P1],"-")=Table36[[#Headers],[Randy]],""))

Any ideas why TEXTBEFORE isn’t working?

Fig. 1
Schedule Forum.png

Fig. 2
Instructors Schedule Forum.png
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Can you post your sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
I attempted to install the Add-in, but I'm on a work computer and they don't allow macros.
 
Upvote 0
In that case can you just copy & paste the data.
 
Upvote 0
Fig. 1
LastFirstP1P2P3P4P5
Doe 07/10JaneDonR-CFA
Carter 05/30JimmyAnna-A TAnna-A TJoseph-O&MRaven-P MRandy-Brl
Clark 09/30PetulaJoseph-O&MRaven-P MKimberly-HMRandy-BrlDanny-J R
Field 10/31SallyKimberly-H MRandy-BrlJames-A TJoseph-O&MDanny-J R
Green 04/03JosephJames-A T
Jackson 05/15JanetManny-CFAKimberly-CFARandy-CFADanny-CFARaven-CFA
Jagar 07/24MickTricia-A T
James 06/02SonnyDanny-W EMari-A TMari-A TKimberly-H MRaven-P M
Johnson 04/03MikeTricia-A T
Johnson 07/10FrankDanny-W EDanny-W EJoseph-O&M
Taylor 07/22JamesTricia-ATR
Kennedy 03/06JackieAnna-CFAAnna-CFAKimberly-CFADanny-CFAMari-CFA
Ryan 07/24JackTricia-ATRMari-A T
Mouse 05/01MinnieJoseph-CFAKimberly-CFAJames-CFADanny-CFARandy-CFA
Turner 07/25TinaRandy-BrlManny-A TManny-A TDanny-W EDanny-W E
Sneed 06/12SamRandy-BrlJoseph-O&MDanny-W EDanny-W EJames-A T
Perry 04/17Kimberly-H MRandy-BrlRaven-P MJames-A TDanny-J R

Fig. 2
PeriodTimeRandyMarinelaKimberlyJamesDannyDonR
P18:15TinaBrl, SamBrlSallyH M, H MJosephA TSonnyW E, FrankW EJane-CFA
P29:30SallyBrl, BrlSonnyA T, JackA TJanetCFA, MinnieCFAFrankW E
P311:00JanetCFASonnyA TPetula-HM, JackieCFASallyA T, MinnieCFASamW E
P41:30PetulaBrlSonnyH MA TJanetCFA, JackieCFA, MinnieCFA, TinaW E, SamW E
P52:40JimmyBrl, MinnieCFAJackieCFASamA TPetulaJ R, SallyJ R, TinaW E, J R
 
Upvote 0
Thanks for that, it's because of the blank cells so you need to use
Excel Formula: 
=TEXTJOIN(", ",1,FILTER(AT_VISP[First]&TEXTAFTER(AT_VISP[P1],"-"),TEXTBEFORE(AT_VISP[P1],"-",,,"")=Table36[[#Headers],[Randy]],""))
 
Upvote 0
Another option so the formula can be dragged down & across
Fluff.xlsm
ABCDEFGHIJKLMNOP
1LastFirstP1P2P3P4P5PeriodTimeRandyMarinelaKimberlyJamesDannyDonR
2Doe 07/10JaneDonR-CFAP108:15TinaBrl, SamBrl SallyH M, H MJosephA TSonnyW E, FrankW EJaneCFA
3Carter 05/30JimmyAnna-A TAnna-A TJoseph-O&MRaven-P MRandy-BrlP209:30SallyBrl, Brl JanetCFA, MinnieCFA FrankW E 
4Clark 09/30PetulaJoseph-O&MRaven-P MKimberly-HMRandy-BrlDanny-J RP311:00JanetCFA PetulaHM, JackieCFASallyA T, MinnieCFASamW E 
5Field 10/31SallyKimberly-H MRandy-BrlJames-A TJoseph-O&MDanny-J RP401:30PetulaBrl SonnyH MA TJanetCFA, JackieCFA, MinnieCFA, TinaW E, SamW E 
6Green 04/03JosephJames-A TP502:40JimmyBrl, MinnieCFA  SamA TPetulaJ R, SallyJ R, TinaW E, J R 
7Jackson 05/15JanetManny-CFAKimberly-CFARandy-CFADanny-CFARaven-CFA
8Jagar 07/24MickTricia-A T
9James 06/02SonnyDanny-W EMari-A TMari-A TKimberly-H MRaven-P M
10Johnson 04/03MikeTricia-A T
11Johnson 07/10FrankDanny-W EDanny-W EJoseph-O&M
12Taylor 07/22JamesTricia-ATR
13Kennedy 03/06JackieAnna-CFAAnna-CFAKimberly-CFADanny-CFAMari-CFA
14Ryan 07/24JackTricia-ATRMari-A T
15Mouse 05/01MinnieJoseph-CFAKimberly-CFAJames-CFADanny-CFARandy-CFA
16Turner 07/25TinaRandy-BrlManny-A TManny-A TDanny-W EDanny-W E
17Sneed 06/12SamRandy-BrlJoseph-O&MDanny-W EDanny-W EJames-A T
18Perry 04/17Kimberly-H MRandy-BrlRaven-P MJames-A TDanny-J R
Master
Cell Formulas
RangeFormula
K2:K6K2=LET(c,CHOOSECOLS(AT_VISP[[P1]:[P5]],XMATCH(Table36[@[Period]:[Period]],AT_VISP[[#Headers],[P1]:[P5]])),TEXTJOIN(", ",1,FILTER(AT_VISP[[First]:[First]]&TEXTAFTER(c,"-"),TEXTBEFORE(c,"-",,,,"")=Table36[[#Headers],[Randy]],"")))
L2:L6L2=LET(c,CHOOSECOLS(AT_VISP[[P1]:[P5]],XMATCH(Table36[@[Period]:[Period]],AT_VISP[[#Headers],[P1]:[P5]])),TEXTJOIN(", ",1,FILTER(AT_VISP[[First]:[First]]&TEXTAFTER(c,"-"),TEXTBEFORE(c,"-",,,,"")=Table36[[#Headers],[Marinela]],"")))
M2:M6M2=LET(c,CHOOSECOLS(AT_VISP[[P1]:[P5]],XMATCH(Table36[@[Period]:[Period]],AT_VISP[[#Headers],[P1]:[P5]])),TEXTJOIN(", ",1,FILTER(AT_VISP[[First]:[First]]&TEXTAFTER(c,"-"),TEXTBEFORE(c,"-",,,,"")=Table36[[#Headers],[Kimberly]],"")))
N2:N6N2=LET(c,CHOOSECOLS(AT_VISP[[P1]:[P5]],XMATCH(Table36[@[Period]:[Period]],AT_VISP[[#Headers],[P1]:[P5]])),TEXTJOIN(", ",1,FILTER(AT_VISP[[First]:[First]]&TEXTAFTER(c,"-"),TEXTBEFORE(c,"-",,,,"")=Table36[[#Headers],[James]],"")))
O2:O6O2=LET(c,CHOOSECOLS(AT_VISP[[P1]:[P5]],XMATCH(Table36[@[Period]:[Period]],AT_VISP[[#Headers],[P1]:[P5]])),TEXTJOIN(", ",1,FILTER(AT_VISP[[First]:[First]]&TEXTAFTER(c,"-"),TEXTBEFORE(c,"-",,,,"")=Table36[[#Headers],[Danny]],"")))
P2:P6P2=LET(c,CHOOSECOLS(AT_VISP[[P1]:[P5]],XMATCH(Table36[@[Period]:[Period]],AT_VISP[[#Headers],[P1]:[P5]])),TEXTJOIN(", ",1,FILTER(AT_VISP[[First]:[First]]&TEXTAFTER(c,"-"),TEXTBEFORE(c,"-",,,,"")=Table36[[#Headers],[DonR]],"")))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

B
  • Solved
Extracting a specific number of characters from a multiline cell
Replies
7
Views
377
etaf
etaf
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
T
  • Question Question
get table row and column headings based on multiple cells with the same value
Replies
6
Views
428
KRice
K
J
  • Solved
Filter function with multiple and|or criteria not working
Replies
3
Views
586
Peter_SSs
Peter_SSs
O
  • Solved
Return a different column from FILTER function. OFFSET and FILTER required?
Replies
7
Views
153
ollyhughes1982
O

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top