Forum Discussion
Extraction formula help
I need a formula to extract the AC-UG from the text string below and then put them in two separate columns without using the text-to-columns feature - TIA! EM-AC-UG-Course-ENG-122-Week3-TOT
Could we back up a little here? I can't believe this is the only such extraction & reconstruction you're wanting to do. It's got be a first example of a bunch of such extraction & reconstruction & relocation tasks.
That is to say, I suspect you've got a number of rows of similar course codes.....perhaps not all with the precise letters "AC-UG" to be extracted. So the formulas (it'll be at least two, one for each of the new columns) may have to be a little smarter than "FIND the string 'AC-UG'" and pull it out for one column....
It would help, therefore if you'd give us the "bigger picture." Also what version of Excel are you working with? You make a point of not wanting to use Text-to-Column, but I wonder if (for example) you have a new enough version so that we could use the recent LET function? (Excel 2021 or later required)
- mathetes
yes, sorry I should've added more info. -- Here is the column of data that I am working with. I need to extract the second and third values from each text string. The second value represents the department, and the third value represents the student program level.
EM-AC-UG-Course-107-WebinarSeries-scheduleWks5-8
EM-AC-UG-Course-ENG-122-Week5-TOT
EM-AC-UG-Program-BSCJ-Career-Mentor-Invite-TOT
EM-AC-UG-Intro-Academic-Support-TOT
EM-AC-UG-Course-ENG-123-Week3-TOT
EM-AC-UG-Course-ENG-122-Week3-TOT
EM-AC-UG-Course-107-WebinarSeries-Wk4
EM-AC-UG-Course-107-WebinarSeries-Wk3
EM-AC-UG-Course-107-WebinarSeries-Wk2
EM-AC-UG-Course-107-WebinarSeries-scheduleWks1-4
EM-AC-UG-Course-ENG-123-Week5-TOT
EM-AV-CBE-Hurricane_Ian-20220927
EM-AV-GRUG-Hurricane_Ian-20220927
EM-AV-UG-RE_Wk4_PreRecode-TOT
EM-AV-UG-RE_Wk4_PreRecode-TOT
EM-AV-CBE-Hurricane_Ian-20220927
EM-AV-GRUG-Hurricane_Ian-20220927
EM-AV-UG-RE_Wk4_PreTerm_FinancialAidTips-TOT
EM-AV-UG-MilitaryTA_Reminder1_Dyn_Branches_TOT
EM-AV-UG-CWO-Mid-Term-CheckIn-TOT
EM-AV-UG-NUR300_PreTerm-TOT
EM-AV-UG-NUR325_Shadow_Health-TOT
EM-AV-UG-RE_Wk5_PreTerm_PushtoEngaged_TOT
EM-AV-UG-RE_Wk5_PreTerm_PushtoEngaged_TOT
EM-AV-CBE-End_of_Term_Reminder-22DA06
EM-AV-UG-Advanced_Reg_Reminder_TOT
EM-AV-UG-Advanced_Reg_Reminder_GUILD_TEST
EM-AV-GRUG-Conferral-ClapLine-TOT
EM-AV-UG-SNHU-107-First-Time-Notice-Wk3-TOT
EM-AV-UG-SNHU-107-Retake-Notice-Wk3-TOT
EM-AV-GR-AEP_AODOC_Term1_Week4_TOT
EM-AV-GR-AEP_AODOC_Term2_Week4_TOT-20210601
EM-AV-CBE-Advanced-Registration-Reminder-22DA06
EM-AV-UG-RE-Dusty-22EW2-20220913
EM-AV-UG-RE-Dusty-22EW2-20220913
EM-AV-CBE-NonPar_Admin_Withdrawal_22DA08
EM-AV-CBE-NonPar_Admin_Withdrawal_22DA08
EM-AV-CBE-NonPar_Reminder3_22DA08
EM-AV-UG-Petition-to-Graduate-20210909
EM-AV-CBE-NonPar_Reminder2_22DA08
EM-AV-UG-RE_Wk2_NotEnrolled_Reassignment_TOT
EM-AV-UG-RE_Wk2_NotEnrolled_Reassignment_TOT
EM-AV-UG-RequiredtoDiscussChangeofMajor_BS.ACC_BS.ACC.FIN
EM-AV-UG-RE-Wk2-Transition-To-Advising-TOT
EM-AV-UG-NonPar-Withdrawal-Week2-TOT
EM-AV-UG-NonPar-Withdrawal-Week2-TOT
EM-AV-CBE-NonPar_Reminder1_22DA08
EM-AV-UG-NonPar_Sunday_TOT
EM-AV-UG-NonPar_Friday_TOT
EM-AV-GR-Petition_to_Graduate_TOT-20210520
EM-AV-CBE-NonPar-Jumpstart-22DA08
EM-AV-UG-Learning_Materials_Retakes-TOT
EM-AV-UG-NonPar-Thursday-WorkDue-TOT
EM-AV-GRUG-HelpU_Triggered_Send
EM-AV-GRUG-HelpU_Triggered_Send
EM-AV-UG-MAT_Min_Grade_Requirement-TOT
EM-AV-UG-AEP_Term2_Week4_TOT-20210525
EM-AV-UG-AEP_Week4_Term1_TOT-20210525
EM-AV-UG-UndergradToGrad_12_creditsRemaining_TOT-20220720
EM-AV-UG-NUR325_Week5-TOT
EM-AV-GRUG-NUR545_Requirements-TOT
EM-AV-GRUG-NUR545_Shadow_Health-TOT
EM-AV-UG-Special-Materials-TOT
EM-AV-UG-AStoBS-9to12-Credits-Remaining-TOT
EM-AV-UG-AStoBS-3to6-Credits-Remaining-TOT
EM-AV-GR-New_Student_Director_Welcome_TOT-20210518
EM-AV-UG-New-Student-Director-Welcome-TOT
EM-AV-UG-Time-Management-Wk2-TOT
EM-AV-GR-Mid_Term_CheckIn_ToT
EM-AV-UG-Course-Materials-Registered-TOT
EM-AV-UG-UndergradToGrad_30_creditsRemaining_TOT-20210324
EM-AV-UG-ChangeofMajor_Labkits
EM-AV-GRUG-Registration-Confirmation-Notification-V2
EM-CI-CBE-End_of_Term_Reminder-22DA06
EM-CI-CBE-NonPar_Admin_Withdrawal_22DA08
EM-CI-CBE-NonPar_Admin_Withdrawal_22DA08
EM-CI-CBE-NonPar_Reminder3_22DA08
EM-CI-CBE-NonPar_Reminder2_22DA08
EM-CI-CBE-NonPar_Reminder1_22DA08
EM-CI-CBE-NonPar-Jumpstart-22DA08
EM-CR-GRUG-UC_Career_Fair-20220930
EM-CR-GRUG-Military_Family_Job-Fair_Invite-20220921
EM-CR-UG-Student_Efficacy_Report_Survey-20220913
EM-CR-UG-Student_Efficacy_Report_Survey-20220913
EM-CR-GRUG-Business_Vertical_Series-20220912
EM-CR-UG-Student_Efficacy_Report_Survey-20220906
EM-CR-UG-Student_Efficacy_Report_Survey-20220906
EM-CR-GRUG-Monthly_Post_Grad_Survey
EM-CR-GRUG-Monthly_Post_Grad_Survey
EM-CR-GR-Internship_Paperwork_1M_Reminder-TOT
EM-CR-GR-Internship_Paperwork_1M_Reminder-TOT
EM-CR-UG-Career_Student_Lifecycle_V5-ToT
EM-CR-UG-Career_Student_Lifecycle_V4-ToT
EM-CR-UG-Career_Student_Lifecycle_V3-ToT
EM-CR-UG-Career_Student_Lifecycle_V2-ToT
EM-CR-UG-Career_Student_Lifecycle_V1-ToT
EM-CR-GR-Career_Student_Lifecycle_V3-ToT
EM-CR-GR-Career_Student_Lifecycle_V2-ToT
EM-CR-GR-Career_Student_Lifecycle_V1-ToT
EM-CR-GRUG-InternshipSeries_ALL_Email3-20210310
EM-CR-GRUG-InternshipSeries_ALL_Email3-20210310
EM-DS-CBE-CI-New_Start_Survey-TOT
EM-DS-CBE-CI-New_Start_Survey-TOT
EM-DS-GR-CX_Volunteer_Request-TOT
EM-DS-UG-CX_Volunteer_Request-TOT
EM-HU-CBE-Minimize-Worry-20220912
EM-HU-GRUG-Minimize-Worry-20220912
EM-RG-GRUG-Anticipated-Conferral-Letter-TOT
EM-RG-GRUG-Pre-Conferral-Diploma-Details-TOT
EM-SC-CBE-Connect-Invite-NewStarts-TOT
EM-SC-UG-Connect-Invite-NewStarts-TOT
EM-SC-GR-Connect-Invite-NewStarts-TOT
EM-SFS-GRUG-Guild_FAFSA-20220729
EM-SFS-GRUG-Guild_FAFSA-20220729
EM-SFS-GRUG-Past_Due_Balance1-20221003
EM-SFS-GRUG-VR&E_Renewal-20220318
EM-SFS-GRUG-VR&E_Renewal-20220318
EM-SFS-GRUG-Missing_MPN-ELC-TOT
EM-SFS-GRUG-Missing_MPN-ELC-TOT
EM-SFS-GRUG-Refund_Series-20220316
EM-SFS-GRUG-Refund_Series-20220316
EM-SFS-UG-Registration-Exception-Reminder-TOT
EM-SFS-GRUG-Financial_Aid_Award-20220802
EM-SFS-GRUG-Financial_Aid_Award-20220802
EM-SFS-GRUG-Refund_Series-20220316
EM-SFS-GRUG-Refund_Series-20220316
EM-SI-GRUG-Club-Cabinet-Video-202209270
EM-SI-CBE-Club-Cabinet-Video-20220927
EM-SI-CBE-Online-Student-Life-Webinars-20220907
EM-SI-GRUG-Online-Student-Life-Webinars-20220907
EM-SI-CBE-Homecoming-Invite-20220930
EM-SI-GRUG-Homecoming-Invite-20220930
EM-AC-GRUG-Historical-Background-Podcast-Invite-20220919
EM-AC-UG-Course-FYE-101-Webinar-Invite-TOT
EM-AC-GRUG-Healthcare-Global-Summit-Invite-20220928
EM-AC-UG-Course-FYE-101-Webinar-Reminder-TOT
EM-AC-UG-Course-FYE-101-Webinar-Invite-TOT
EM-SI-GRUG-Delta_Mu_Delta_Invite-20220928
EM-COM-GRUG-Cleary-Act-Annual-Security-Report-20220927
EM-COM-CBE-Cleary-Act-Annual-Security-Report-20220927
EM-COM-UC-Cleary-Act-Annual-Security-Report-20220927
EM-SI-GRUG-Chicago-Meet-Up-Confirmation-20220927
EM-AC-GR-22TW1-Program-MSBANCOH-Digital-Badging-20220926
EM-AC-GRUG-Brightspace-LMS-Survey-20220915
EM-SI-CBE-Washington-DC-Meet-Up-20220923
EM-SI-GRUG-Washington-DC-Meet-Up-20220923
EM-ODI-GRUG-Black-New-England-Conference-Invite-20220922
EM-ODI-CBE-Black-New-England-Conference-Invite-20220922
EM-AC-GRUG-22TW2-HEaRT-Soc-Sci-Invite-20220922
EM-AC-GRUG-HP-Cares-FBI-Invite-20220921
EM-AC-GRUG-HP-Cares-FBI-Invite-20220921
EM-AC-GRUG-22TW2-HEaRT-HC-Invite-20220920
EM-AC-GRUG-Historical-Background-Podcast-Invite-20220919
EM-SI-GRUG-Chicago-Meet-Up-20220916
EM-SI-CBE-Chicago-Meet-Up-20220916
EM-AC-GRUG-Brightspace-LMS-Survey-20220915
EM-AC-UG-FYE-Peer-Mentor-Invite-TOT
EM-AC-GRUG-Program-BSN-MSN-Town-Hall-Invite-20220914
EM-AC-GRUG-Program-BSN-MSN-Town-Hall-Invite-20220914
EM-AC-GRUG-Social-Sciences-Lecture-Invite-20220914
EM-AC-GRUG-Social-Sciences-Lecture-Invite-20220914
EM-AC-UG-22EW2-HEaRT-Solutions-Lab-Invite-20220913
EM-SI-GRUG-Honor-Society-STD-Invite-20220908
EM-AC-GRUG-Program-MFA-Wireside-Chat-Invite-20220906
EM-AC-GRUG-Program-MFA-Wireside-Chat-Invite-20220906
EM-AC-UG-FYE-Peer-Mentor-Reminder-TOT
EM-EV-GRUG-Commencement-Fall2022-Reminder1
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Invite
EM-EV-GRUG-Commencement-Fall2022-Check-SNHU-Email
EM-EV-GRUG-Commencement-Fall2022-SaveDate-2
EM-SFS-UC-Bills_Past_Due-20220824
EM-SFS-UC-Health_Insurance_Waiver-20220913
EM-SFS-UC-Health_Insurance_Waiver-20220913
EM-SFS-UC-Missing_MPN-ELC-TOT
EM-SFS-UC-Missing_MPN-ELC-TOT
EM-SFS-UC-Missing_MPN-ELC-TOT
EM-SFS-UC-Missing_MPN-ELC-TOT
EM-UC-AV-AEPReminder-TermStart-Week1
EM-UC-AV-AEPReminder-TermStart-Week4
EM-UC-AV-Faculty_Advisor_Intro-20220922
EM-UC-AV-GR-Week_Four_Checkin-20220926
EM-UC-AV-UG-NonPar-Monday-Withdrawal-TOT
EM-UC-AV-UG-NonPar-Sunday-TOT
EM-UC-AV-UG-NonPar-Thursday-TOT
