Oct 17 2022 09:30 AM
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
Oct 17 2022 10:09 AM
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)
Oct 17 2022 10:09 AM
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.
Oct 17 2022 10:10 AM
Oct 17 2022 10:22 AM
Oct 17 2022 10:25 AM
Oct 17 2022 11:07 AM
I've copied your data and pasted it in the blue table. Then i clicked in any cell of the green table, right-clicked with the mouse and selected refresh.
There are several free tutorials on the internet if you want to start with Power Query. I personally learned a lot about Power Query by the solutions provided by the experts of the Microsoft Tech Community.
In the attached file you can view the Power Query Editor, the applied steps and formulas:
In the Power Query Editor you can follow the steps of data manipulation:
You can as well view the formulas that created the query:
Oct 17 2022 11:25 AM
- 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
ToTable
And 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
)
Oct 17 2022 11:47 AM - edited Oct 17 2022 11:48 AM
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
Oct 17 2022 12:30 PM - edited Oct 17 2022 12:31 PM
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)
)
Oct 17 2022 02:44 PM
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 Sub
Another alternative could be VBA code. In the attached file you can click the button in cell F2 to run the macro.