[Solved] Extract text between delimiters

[MtnGolferCO]

I've been at this for a few days and just cannot see the typo(s). Any & all help will be greatly appreciated!

I have a spreadsheet wiih high school football game schedules NS Cell K(x) has the raw data Team1 at Team 2, time, location, etc.

I am trying to get just the Home Team name between " at " and a "," I have tried a 2nd "FIND" for the ending character but get the same #VALUE error


=MID(LEFT(K3;(FIND(" at"+4); FIND(",";K3;20))))
=MID(LEFT(K3;(FIND(" at"+4); FIND(",";K3)))

TIA!!!!

Tom

[robleyd]

Can you upload a sample spreadsheet showing the data you are working with and what you are trying to achieve?

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here.

[MtnGolferCO]

Incomplete raw data:
Wk Day Date GmTime VTFN HTFN VTAbrv HTAbrv TVNtwk Location
1 Thu 09/09/21 Landon Bears St Albans School , 8:20 p.m. (NBC) LAN STA
1 Sun 09/12/21 St Albans School St Stevens School, STA SSE

I am trying to get HTFN for each line

[robleyd]

It isn't clear - to me at least, possibly as it is late here - what data fits with what column headings in your example. As suggested, can you provide a sample spreadsheet file.

[MtnGolferCO]

This is a two row sample of the spreadsheet:

Wk--Day---Date------GmTime-------VTFN----------------------------------------HTFN------VTAbrv--HTAbrv
1--Thu--09/09/21-- 11:00am---Landon School-@-St Albans School ,---------------------LAN-------STA ( I am trying to break out the Home Team Full Name to it's own column
1--Sat--09/12/21--- 11:00am---St Albans School--@-St Stevens School,-----------------STA--------SSE

HTFN=Home Team Full Name

[MtnGolferCO]

I am using the dashes as space holders, this site removes whitespace. I am trying to get everything(vaiable lengths) between the "at" and "," characters.

[Bill]

Without a sample spreadsheet, I'm guessing. If you save the spreadsheet in CSV format and close it, when you reopen the CSV file you can specify the @ symbol as a column separator.

[MtnGolferCO]

i HAVE SUPPLIED A SAMPLE SPREADSHEET MULTIPLE TIMES NOW.

[Bill]

What you supplied are word pictures. A sample spreadsheet is a real spreadsheet document file in ODS or whatever spreadsheet format you're using.

Did you try my suggestion?

[MtnGolferCO]

Due to legal restrictions, the sample data rows is all I can provide, It's seems like a simple question and two rows should be a suficient sample of the data & problem/question.

I have saved all files as .csv's and this is still the situation. The MID(LEFT) command still provides a "#VALUE" result.

[Villeroy]

Why do you load text data (csv) into a spreadsheet program? This makes no sense, particularly when you don't know much about spreadsheet formulas.

[MtnGolferCO]

It was a reply to Bill's idea.

[MtnGolferCO]

SOLVED

=MID(K2;FIND(" at ";K2)+4;FIND(",";K2)-FIND(" at";K2)-4)

[Bill]

In my attempt to create my own sample so I could test it, I missed the commas in the data rows. After adding the commas, the cells containing the commas were enclosed in quotes when saving in CSV format and the @ symbols in those cells were no longer recognized as column separators. I had to change the separators from comma to semi-colon when saving and opening the CSV file to work around that problem. I did not have to use any formulas to get the home team name into a separate column.