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
The custom function AnyPart can do that.
It is part of the free "Custom_Functions" Excel add-in.(20+ excel functions that work exactly like the built-in functions)
(filled across and then filled down)
Pay attention to the placement of the $ symbols.
Download the add-in from OneDrive...
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
A separate examples file is also available.
'---
Nothing Left to Lose
I have used the LET function in the attached. So you will need the newer version of Excel.
Other than that, the two formulas use the most basic of Text functions, FIND, LEFT, MID and RIGHT. I've attached a spreadsheet with your extractions done.
This formula that extracts the second and third sets of characters is
=LET(
strt,FIND("-",A1)+1,
midl,FIND("-",A1,strt),
fnl,FIND("-",A1,midl+1),
MID(A1,strt,fnl-strt)
)
No doubt there are more elegant ways to do this, but I assume you'll be able to follow that.
And this one concatenates the remainder into a single next, leaving out the extracted text.
=LET(
hypa,FIND("-",A4),
hypb,FIND("-",A4,hypa+1),
hypc,FIND("-",A4,hypb+1),
LEFT(A4,FIND("-",A4))&RIGHT(A4,LEN(A4)-hypc)
)
- Thanks OliverScheurich for copying/pasting your data and sharing the file
- You did not answer mathetes question re. version of Excel you use (this is important)
Attached is another Power Query option:
let Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], SplitInList = Table.AddColumn(Source, "LIST", each List.FirstN(List.Skip(Text.Split([Spalte1], "-")), 2) ), ToTable = Table.FromRows(SplitInList[LIST]) in ToTableAnd a 365 worksheet formula (spills as dynamic array) based on the same Table:
=DROP( REDUCE(0,Tabelle1[Spalte1], LAMBDA(seed,current, VSTACK(seed, TEXTSPLIT(TEXTAFTER(TEXTBEFORE(current,"-",3),"-"),"-")) ) ), 1 )- I opened your attachment, and this is what I need returned...But I do not see the formula and how you produced this. Also, could you explain a little more about the Power Query option?
Sub extract() Dim i As Long Dim j As Long Dim first As Long Dim second As Long Dim third As Long j = Range("B" & Rows.Count).End(xlUp).Row For i = 2 To j first = Application.WorksheetFunction.Find("-", Cells(i, 2)) second = Application.WorksheetFunction.Find("-", Cells(i, 2), first + 1) third = Application.WorksheetFunction.Find("-", Cells(i, 2), second + 1) Cells(i, 3).Value = Mid(Cells(i, 2), first + 1, second - first - 1) Cells(i, 4).Value = Mid(Cells(i, 2), second + 1, third - second - 1) Next i End SubAnother alternative could be VBA code. In the attached file you can click the button in cell F2 to run the macro.
Let's say you have such text strings in B2 and down.
For the first part:
=TRIM(MID(SUBSTITUTE(B2,"-",REPT(" ",255)),256,255))
For the second part:
=TRIM(MID(SUBSTITUTE(B2,"-",REPT(" ",255)),512,255))
Fill down.
Alternatively, enter AC in a cell next to the cell with the fill string, then select Fill > Flash Fill on the Home tab of the ribbon.
Do the same with UG in the cell next to the one with AC.
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
