Hi sniffer66
That was my point
But it works
Rich1950
Chat GPT & Generative AI tools
Hi RichRich1950 wrote: ↑Mon Dec 18, 2023 2:39 pm
If the text "Arsenal v Levercusen" has a "v" within "Levercusen," and you want to split it into two columns at the first occurrence of "v," you can use a formula in Excel. Assuming your data is in cell A1, you can use the following formulas:
For the "Arsenal" part (in cell B1):
```excel
=LEFT(A1, SEARCH(" v", A1) - 1)
```
For the "Levercusen" part (in cell C1):aa
```excel
=MID(A1, SEARCH(" v", A1) + 2, LEN(A1))
```
Rich1950
I know this is about AI but there is a brilliant cell formula which will work on newer versions of Excel, called TEXTSPLIT which may help some people.
Instead of using LEFT and MID in scenarios like above, use TEXTSPLIT instead you have your text, "Arsenal v Levercusen"in cell A1, so in cell B1 you would insert
=TEXTSPLIT(A1," v ")
This will automatically put "Arsenal" in cell B1 and "Levercusen" in cell C1.
yup that would work. and a small extra: =PROPER(TEXTSPLIT(LOWER(A1)," v "))conduirez wrote: ↑Mon Dec 18, 2023 4:40 pmHi RichRich1950 wrote: ↑Mon Dec 18, 2023 2:39 pm
If the text "Arsenal v Levercusen" has a "v" within "Levercusen," and you want to split it into two columns at the first occurrence of "v," you can use a formula in Excel. Assuming your data is in cell A1, you can use the following formulas:
For the "Arsenal" part (in cell B1):
```excel
=LEFT(A1, SEARCH(" v", A1) - 1)
```
For the "Levercusen" part (in cell C1):aa
```excel
=MID(A1, SEARCH(" v", A1) + 2, LEN(A1))
```
Rich1950
I know this is about AI but there is a brilliant cell formula which will work on newer versions of Excel, called TEXTSPLIT which may help some people.
Instead of using LEFT and MID in scenarios like above, use TEXTSPLIT instead you have your text, "Arsenal v Levercusen"in cell A1, so in cell B1 you would insert
=TEXTSPLIT(A1," v ")
This will automatically put "Arsenal" in cell B1 and "Levercusen" in cell C1.
this would take a value of "Arsenal V Levercusen" and split it irrespective of case and then convert the team names to proper case...
- paspuggie48
- Posts: 662
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
- paspuggie48
- Posts: 662
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
Lots of new functions in Excel like Textsplit are great for one off things. Textjoin is another
Would be much easier with PQ but you probably know I would say that.
As per what Sniffer said, I use the "space v space" as a delimiter. In fact one can write whatever one wants in the open text field.
Here is a list of games exported from Guardian.
One can use the Split Column function in the Power Query Editor
The outcome being something like this : -
There are other options like text before or after delimiter, ranges, number of characters. There so many options and as I have mentioned before it requires no formulas at all.
Just saying...
You do not have the required permissions to view the files attached to this post.