SOLVED

Searching/Finding specific text in field and including them

Copper Contributor

Hey,
I have a row with ad names like "Video_8_2021-12-01 12:00:29" or "Video_7_2021-12-01 12:00:29".

I want to make a new row only with the real names like "Video_8" or "Video_7".
17.12.PNG

Currently it is only possible for me to do it, with one row. This is my function currently 

=WENN(ISTFEHLER(FINDEN("Video_1";B6;1));"";"Video_1")=WENN(ISTFEHLER(FINDEN("Video_1";B6;1));"";"Video_1")

Do you know how I can solve my problem and do it for alle rows and use a  function so that in the end there is the row like:

Video_1
Video_2
Video_7
Video_2
etc...

Thank you and best regards

6 Replies

@jeffraybo Have a look at the attached file. It will translate into German when you open it.

 

Screenshot 2021-12-17 at 15.08.41.png

@jeffraybo 

As done in the attached file you can manually type "Video_8" in cell c6 and then apply flashfill by pressing ctrl+E.

best response confirmed by jeffraybo (Copper Contributor)
Solution
thank you, this is pretty cool!
thank you! this is really helpful.

but I dont know if I really understand the function.

So the function is looking for the "_" sign after the 7 letters on the left side?
and -1 so that the "_" isnt in the a field included or?

@jeffraybo Precisely. Personally, I favour such a formula over Flash Fill. But that's just my own preference.

@jeffraybo 

In addition to @Riny_van_Eekelen comment. FlashFill, especially on relatively large or not well structured data could give incorrect results. It have to check carefully what is returned by FlashFill to be sure result is correct.

1 best response

Accepted Solutions
best response confirmed by jeffraybo (Copper Contributor)
Solution
thank you, this is pretty cool!

View solution in original post