Avatar billede lail Forsker
07. december 2018 - 09:36 Der er 34 kommentarer og
3 løsninger

funktion hvis tekst streng indeholder

Har i kolonne A nogle betegnelser der er ca 4000 linier
1./IDBM1a1234
3./ADPM5b1458
2./ADBM2c4589
4./HDCM1a4781
2./IDPM3a1222
7./ADDE1a1444
3./ADENT3a4555

Jeg vil gerne i kolonne B have Hvis A indeholder BM , så skal den skrive BM osv
Der skal søges på BM, UM, CM,DE,ENT, SD,KD;VF,RD
Nogle der kan hjælpe mig med denne formel

Og hvis ikke hvis ikke der kommer et resultat skal der stå bulp

LN
Avatar billede Max_P_Larsen Seniormester
07. december 2018 - 10:18 #1
Jeg tænker, at dette er bedst gjort med en makro/VBA-kode, hvor du i en tabel tilføjer alle de forskellige koder/søgetekster, som så loop'es igennem linje for linje. Ellers kommer du ud i en lang og mindre overskuelig formel med mange indlejrede HVIS-formler, som er besværlig at vedligeholde, når/hvis der kommer nye koder/søgetekster.

Som formel bliver det noget i stil med

=HVIS(IKKE(ER.FEJL(SØG("*BM*";A6)));"BM";HVIS(IKKE(ER.FEJL(SØG("*PM*";A6)));"PM"))

Denne skal så udbygges til at håndtere alle kode/søgetekster.
Avatar billede kim1a Ekspert
07. december 2018 - 11:08 #2
Du kunne med støttekolonner slippe lidt for det. Hvis de bare skal markeres så lav en kolonne for hver søgestreng. Så kan du lave en sumformel yderst og sætte betinget formattering på tal over nul. Eller blot søge/filtrere på der hvor der er noget over nul.

VBA vil kunne f.eks. kopiere dem over til et sted, eller blot igen markere/skrive i kolonne B, men det virker som lidt meget for "kun" 4000 linjer, medmindre der er andre som skal bruge udvælgelsen.
Avatar billede Max_P_Larsen Seniormester
07. december 2018 - 11:15 #3
#2
kim1a - Jeg har forsøgt med en SUMPRODUKT-formel, men jeg kan kun få den til at "konstatere", om en eller flere af søgeteksterne indgår - men ikke hvilken tekst, det præcis er. Har du et bud på sådan en type løsning?
Avatar billede Nikolaj Forsker
07. december 2018 - 11:28 #4
Er det et tilfælde i din eksempel at det du søge efter, altid tid starte samme sted i cellen ?

Eller er det altid sådan at det starter ved tegn 6 ?

I såfald kan den første del løses ved
=HVIS(MIDT(A1;6;3)="ENT";"ENT";MIDT(A1;6;2))
Kan dog ikke få "bulp" med endnu. Men kan være andre kan :)
Avatar billede Nikolaj Forsker
07. december 2018 - 11:34 #5
Så kom "Bulp" med. Med den forudsætning at dine søgekriterier starter samme sted.

=HVIS.FEJL(LOPSLAG(HVIS(MIDT(A1;6;3)="ENT";"ENT";MIDT(A1;6;2));$F$1:$F$10;1;FALSK);"bulp")


Jeg har listet søgekriterierne i F1:F10 i Lopslaget.

Kan du evt sætte et andet sted
Avatar billede finb Ekspert
07. december 2018 - 12:15 #6
Select Case
  InStr ,,, BM
  InStr,,, UM
  osv.osv.
End Select
Avatar billede xl-Enthusiast Ekspert
07. december 2018 - 13:19 #7
Avatar billede Jan Hansen Ekspert
07. december 2018 - 13:37 #8
Hej har lavet en lille bruger def Function
se her.

https://1drv.ms/x/s!AmlqgsyjsAq5hYg_oJ_ckHm_xumK0g

Jan
Avatar billede kim1a Ekspert
07. december 2018 - 13:55 #9
#7 xl-Ent
Det er lækkert det der som jeg ser det. Kan du forklare så vi andre forstår? Blandt andet hvorfor "--isnumber" hvis jeg fjerner de to minus så virker den ikke.

Mit forslag var mere lavpraktisk. Lav en formel som angiver et tal hvis det står i teksten, så skær x karakterer derfra, men det kræver jo en kolonne til hver. Du har sat dem alle i en.
Avatar billede xl-Enthusiast Ekspert
07. december 2018 - 14:19 #10
@kim1a

Hvis du har filen åbnet så prøv for eksempel at vælge cell B5. Marker præcist følgende del af formlen:
ISNUMBER(SEARCH(Crit;A5)) og tryk på F9. Du vil se:
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, som er TRUE i 3 position, Nu har jeg lavet en MATCH funktion hvor første argument er 1, så jeg har brug for at konvertere FALSE/TRUE til 0/1. Det gør jeg ved at sætte dobbelt minustegn foran ISNUMBER. Med celle B5 stadigvæk valgt, marker præcist følgende del af formlen: --ISNUMBER(SEARCH(Crit;A5)) (altså inklusive de to minustegn) og tryk på F9. Du vil nu se: {0;0;1;0;0;0;0;0;0}. Så vi matcher altså 1 (første argument i MATCH funktionen med første udgave af 1 i matricen  {0;0;1;0;0;0;0;0;0} og resultatet er tre, hvilket ses ved at marekere præcist MATCH(1;--ISNUMBER(SEARCH(Crit;A5));0) og trykke på F9. Jeg går ud fra du herefter selv kan analysere resten af formlen. Hvis ikke, så kom igen. (Crit er navnet for område F3:F11).
Avatar billede Max_P_Larsen Seniormester
07. december 2018 - 15:11 #11
#7
Det lader ikke til, at den fanger koden "ENT" i sidste række....??
Avatar billede xl-Enthusiast Ekspert
07. december 2018 - 15:41 #12
Nej, den finder kun første udgave af et kriterie. Det er muligt den skal kunne det du skriver, men det synes jeg ikke fremgik klart af spørgsmålet. Jeg vil ikke sige det er umuligt formel-mæssigt, men jeg har ikke umiddelbart et forslag.
Avatar billede Max_P_Larsen Seniormester
07. december 2018 - 15:54 #13
#12
Måske er det så simpelt som at bytte rundt på rækkefølgen af "DE" og "ENT" i listen, således den først søger efter "ENT".... :-)
Avatar billede Max_P_Larsen Seniormester
07. december 2018 - 15:57 #14
#12 / #13
Det var det, der skulle til :-)
Avatar billede xl-Enthusiast Ekspert
07. december 2018 - 16:06 #15
Men hvad så hvis du tilføjer AD som kriterie?  :-)
Avatar billede lail Forsker
07. december 2018 - 17:40 #16
Man kan ikke bare tilføje ad i kriteriet da det ikke altid er det der står foran.
Det står heller ikke altid samme sted.

Vil helst ikke bruge makro da det er meget vital fil. Den bruges hver dag mange timer af 3 brugere ud over mig. Tror slet ikke de pt kan rumme hvis jeg siger nu skal i gemme med makro

Finb fatter ikke havd du skriver. Hvad står alle dine ,,,, for?:0)
Avatar billede xl-Enthusiast Ekspert
07. december 2018 - 17:52 #17
#16
Nej, men min bemærkning var kun for at sige, at det der står i #14 ikke holder.
Avatar billede xl-Enthusiast Ekspert
07. december 2018 - 18:19 #18
@lail

Kunne du ikke bruge den fil jeg linkede til i #7.
Hvis ikke - hvorfor ikke?
Avatar billede Max_P_Larsen Seniormester
07. december 2018 - 18:21 #19
#17
Hvis listen er sorteret alfabetisk faldende, bør det holde fint.
Avatar billede xl-Enthusiast Ekspert
07. december 2018 - 19:06 #20
@Max_P_Larsen

Jeg tror vi snakker forbi hinanden.
I #12 skrev jeg: Nej, den finder kun første udgave af et kriterie.
I #13 henviste du til #12 og skrev: Måske er det så simpelt som at bytte rundt på rækkefølgen af "DE" og "ENT" i listen, således den først søger efter "ENT"....

Men uanset hvad du gør, hvis teksten indeholder først ENT og senere AD (og AD er et af kriterierne) så vil formlen kun returnere ENT og det var hvad jeg mente med "kun første udgave af et kriterie". Så hvad er detrr, der holder fint? Og hvordan ville du i øvrigt løse den af dig foreslåede ombytning?
Avatar billede lail Forsker
07. december 2018 - 21:08 #21
Listen er ikke sorteret
Avatar billede lail Forsker
07. december 2018 - 21:20 #22
Xl entusiast kan jeg vende tilbage søndag eller mandag - akut og pludseligt vingeskudt med omgangssyge 😫🤢 også derfor jeg ikke har været aktiv

Laila
Avatar billede Max_P_Larsen Seniormester
07. december 2018 - 21:29 #23
#20
Korrekt at vi har talt forbi hinanden.

I #11 skriver jeg:
Det lader ikke til, at den fanger koden "ENT" i sidste række....??

Hertil svarer du, at den kun finder første kriterie. Min pointe var, at den skulle finde "ENT" i stedet for "DE" sådan som i dit udmærkede løsningsforslag i Dropbox (#7).

3./ADENT3a4555 versus 3./ADENT3a4555

I din løsning fandt den "DE" i rækken med "ENT", fordi "DE" står oven over/før "ENT" i det navngivne område Crit. Bytter man rundt på de to, således "ENT" står først, finder den enten "DE" eller "ENT" korrekt i de forskellige rækker.

Jeg har ikke på noget tidspunkt talt om "AD" eller at finde flere kriterier i samme række. "AD" er jo slet ikke nævnt som et kriterie i selve indlægget/spørgsmålet fra lail. Det, der holder, er, at Crit-listen skal sorteres alfabetisk faldende for, at hvert kriterie findes korrekt i relevant række. Sagen var blot, at "ADENT3a4555" indeholder "DE", men D'et hører til "AD", og E'et hører til "ENT", som er kriteriet. Derfor skal der søges efter "ENT" før "DE".

Håber du kan se nu, hvad jeg mener :-)
Avatar billede Jan Hansen Ekspert
07. december 2018 - 22:37 #24
Hej Laila

En brugerdefineret function kræver kun tilladelse til at køre macro og gemme med macro ikke noget med man skal køre nogen da den fungere som de indbyggede.

denne fungerer med en hjælpekolonne med kriterierne i


Dim sTemp As String, rKriterie As Range, rCell As Range

Function Test(TestCelle As String, Kriterie As Range)
    sTemp = TestCelle
    Set rKriterie = Kriterie
    For Each rCell In rKriterie
        If InStr(1, sTemp, rCell.Value) Then Test = rCell.Value
    Next
    If Test = "" Then Test = "bulp"
End Function


Jan
Avatar billede xl-Enthusiast Ekspert
08. december 2018 - 07:43 #25
@Max_P_Larsen

Tekststrengen var: 3./ADENT3a4555
Jeg skrev i #12
Nej, den finder kun første udgave af et kriterie

Som jeg ser det - første kriterie den støder på her er DE og det er hvad formlen returnerer. ENT er næste kriterie som den ikke returnerer. Min bemærkning om AD var fordi jeg troede du mente at tekststrengen skulle ombyttes til: 3./ENTAD3a4556. Det var en fejl fra min side.

I #23 skriver du: Sagen var blot, at "ADENT3a4555" indeholder "DE", men D'et hører til "AD", og E'et hører til "ENT"

Hvorfor det? Der har så vidt jeg kan se ikke været nævnt noget om, at et kriterium altid skal være minimum to bogstaver. Det er mulig, du har ret, men det synes jeg ikke man kan udlede det af det oprindelige spørgsmål.

Undskyld til alle andre der ikke interesserer sig for denne diskussion.
Avatar billede xl-Enthusiast Ekspert
08. december 2018 - 08:00 #26
Glem venligst min sidste sætning i #25. Men hvorfor hører D'et til AD og E'et til ENT. Hvad i det stillede spørgsmål retfærdiggør den konklusion?
Avatar billede Max_P_Larsen Seniormester
08. december 2018 - 20:48 #27
#25 og #26 (xl-Enthusiast)

Ud fra det begrænsede datasæt og listen af bogstavkombinationer, der skulle søges efter, har jeg antaget, at de to første bogstaver i data (efter skråstregen) var et præfiks, der ikke skulle søges på.
"ID", "AD", "HD" - ingen af disse tre kombinationer fremgår af søgelisten.

1./IDBM1a1234 - her skal den finde "BM", som står på listen
3./ADPM5b1458 - her skal der stå "bulp", fordi "PM" ikke står på listen
2./ADBM2c4589 - her skal den finde "BM", som står på listen
4./HDCM1a4781 - her skal den finde "CM", som står på listen
2./IDPM3a1222 - her skal der stå "bulp", fordi "PM" ikke står på listen
7./ADDE1a1444 - her skal den finde "DE", som står på listen
3./ADENT3a4555 - her skal den finde "ENT", som står på listen

Søgelisten:
BM, UM, CM,DE,ENT, SD,KD;VF,RD

Her passer det så med, at søgekriterierne står umiddelbart efter præfikset på to bogstaver men før det første tal i tekststrengen. Det er det mønster, jeg ser, men jeg kan jo tage fejl. Og det er derfor, jeg konkluderer, at D'et hører til "AD" i rækken med "ENT". Søgekriteriernes længde fremgår af listen, dvs. 2 eller 3 karakterer.

Men du kan da have ret i, at jeg har gjort mig antagelser på baggrund af hvilke, jeg udleder et mønster i dataene, som muligvis er forkert. Det ændrer dog ikke på, at dit første løsningsforslag i Dropbox ikke fandt kriteriet "ENT" i den sidste række, hvilket - som hele min pointe var i #13 - kunne løses ved at bytte rundt på "DE" og "ENT" i Crit-listen (og her fik jeg måske ikke gjort det klart nok, at det var Crit-listen, jeg mente).

Jeg vil gerne lige understrege, at det er din løsning med matrix-formlen, som jeg anser for den rigtige løsning til dette indlæg, da den netop ikke anvender VBA, sådan som lail ønsker det. Jeg har på intet tidspunkt haft til hensigt at anfægte din løsning men blot at henlede opmærksomheden på, at ét af søgekriterierne ikke blev opfanget.
Avatar billede xl-Enthusiast Ekspert
09. december 2018 - 09:27 #28
@Max_P_Larsen

Du skriver:
Ud fra det begrænsede datasæt og listen af bogstavkombinationer, der skulle søges efter, har jeg antaget, at de to første bogstaver i data (efter skråstregen) var et præfiks, der ikke skulle søges på.

Jeg har så antaget, at spørgeren faktisk mente det han skrev, at:
Hvis A indeholder BM , så skal den skrive BM osv
Der skal søges på BM, UM, CM,DE,ENT, SD,KD;VF,RD
Avatar billede Max_P_Larsen Seniormester
09. december 2018 - 11:22 #29
#28
Så du mener ikke, at det er "ENT", som skal findes i den sidste række? Vil du så ikke forklare, hvorfor "DE" skulle være det korrekte i den række?
Bare fordi jeg har antaget et præfiks, betyder det da ikke, at jeg ikke har tænkt søgningen, som spørgeren har skrevet. Jeg har blot forsøgt at finde et mønster i dataene for se, hvor søgningen burde finde koderne. Ser du slet ikke dette mønster i dataene?
Avatar billede lail Forsker
10. december 2018 - 10:13 #30
xl-Enthusiast

Det du har sendt i droppx virker super

Men vil du forklare formlen trin for trin - fatter nada
og vil jo gerne lære

En lille krølle - kan man få den til at skrive valg hvis der stå vt
og proj hvis der står sam
Avatar billede xl-Enthusiast Ekspert
10. december 2018 - 13:18 #31
Ved knap nok, hvorfor jeg gør det her, men nu gør jeg det altså.

https://www.dropbox.com/s/xg2dxt27itpu59t/FindTekst_2.xlsx?dl=0
Avatar billede xl-Enthusiast Ekspert
10. december 2018 - 17:15 #32
Her er filen så med den sidste krølle (vt, sam). Se C16:C24.

I filen har jeg været nødt til at indsætte ekstra rækker for at kunne udvide Crit-listen. Det har bevirket, at jeg har været nødt til at rette en masse cellereferencer i forklaringen. Håber jeg har fået det rettet korrekt.

Jeg ved ikke om du har fulgt med i den diskussion jeg havde med Max_P_Larsen. Hvis opgaven skal forstås sådan som han gør det, så skal der ændres noget.

https://www.dropbox.com/s/8jme5kuua0enq59/FindTekst_3.xlsx?dl=0
Avatar billede xl-Enthusiast Ekspert
11. december 2018 - 06:43 #33
Der var stadigvæk enkelte forkerte (og forvirrende) cellereferencer, så her kommer lige en ny fil. Se venligst bort fra de tidligere og brug denne her.

https://www.dropbox.com/s/us080km3h0yw56g/FindTekst_4.xlsx?dl=0
Avatar billede lail Forsker
11. december 2018 - 12:36 #34
xl-Enthusiast tak for din forklaring. Hvor er det fantastisk at du gider - og du får det skrevet, så selv jeg forstår. Du gør det nok, fordi du er et godt menneske der gerne vil lære fra dig. Du kommer helt sikkert med i min aftenbøn. TAK

Lail
Avatar billede lail Forsker
11. december 2018 - 13:23 #35
jeg måtte også bytte om på rækkefølgen i crit  - men så fungerer det også 100%
Hvornår ved man at man skal bruge{}
for bedre at kunne følge alle paranteser byggede jge formlen langsom op i brudstykker
Jeg kunne se at det var efter sammenlign at jeg fik brug for ctrl + shift+enter

lail
Avatar billede xl-Enthusiast Ekspert
12. december 2018 - 07:26 #36
Hvornår ved man at man skal bruge{}
for bedre at kunne følge alle paranteser byggede jge formlen langsom op i brudstykker
Jeg kunne se at det var efter sammenlign at jeg fik brug for ctrl + shift+enter


Det kan jeg ikke svare på med få ord. Matrixformler er et kæmpeområde. Jeg kan kun anbefale at du selv studerer og praktiserer emnet, hvis du er interesseret i det.
Avatar billede xl-Enthusiast Ekspert
12. december 2018 - 07:36 #37
@lail

Inden du eventuelt begynder at studere alt for meget om matrixformler skal jeg for god ordens skyld nævne, at hvis du har Office 365, så vil du inden for relativt kort tid modtage en version af Excel der indeholder det helt nye begreb (i Excel) der hedder Dynamic Arrays. Herefter er det ikke længere nødvendigt at bruge indtastningsmåden: hold Ctrl og Shift nede før tryk på Enter, man kan nøjes med bare at trykke Enter. Jeg har selv modtaget sådan udgave af Excel ved at have tilmeldt mig noget der hedder  "Office Insider program". Der er meget spændende nyt på vej i Excel!
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Vi har et stort udvalg af Excel kurser. Find lige det kursus der passer dig lige her.

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester