Anpassad sök och ersätt i Excel

[Mikael63]

I första hand skulle jag vilja hantera detta med sök och ersätt och då via ett makro. Funkar inte det så fungerar det med formler i hjälpceller (jag kan ju hantera även det i makrot, klistra in formel, utöka, markera, kopiera, klistra in värden, städa osv.)

I kolumn H har jag text som

+12345=678ABCDE_ABC_123_DEF

_ ska ersättas med - förutom det första och jag tänker att det är enklast att köra sök och ersätt av alla först och därefter ersätta tillbaks det första - till _ igen

Det är alltid 15 tecken fram till det tecknet (men efterföljande kan vara olika)

? kan användas vid sökning varför jag skulle kunna söka efter ??????????????- men sedan tar det stopp då jag inte kan ange 15 frågetecken som då inte ska ersättas utan det blir just det i texten, 15 st frågetecken.

Förslag?

 

[Mikael63]

Tror jag hittat formel

=BYT.UT(H10;"-";"_";1)

Snyggare då kanske om man kunde lösa detta utan att först ersätta allt, om man nu använder formler. Kan duga tills vidare.

[MH_]

Om du vet att de 16 första tecknen skall lämnas oförändrade så kan du ju dela upp det i två delar:

=VÄNSTER(A12;16)&BYT.UT(EXTEXT(A12;17;LÄNGD(A12));"_";"-")

Men din lösning är mycket elegantare

=BYT.UT(BYT.UT(A12;"_";"-");"-";"_";1)

Hmm, vad kan jag ha kryssat i fel? jag trodde att jag bevakade Office-tråden här, skumt

 

[Mikael63]

Tack! Blev bra detta!

Verkar inte finnas "markera som bästa svar" här...

[Mikael63]

Nu.. skulle jag vilja att det sista _ ,eller det sista - då om det redan har bytts till det, byts ut till ingenting, alltså inte ens ett mellanslag.

Resultatet av exempel +12345=678ABCDE_ABC_123_DEF_GHI ska då bli +12345=678ABCDE_ABC-123-DEFGHI

 

[MH_]

Formel.

Om du VET att det alltid är det 3:e "-" som skall bytas ut kan du lägga på en byt.ut till

=BYT.UT(BYT.UT(BYT.UT(A12;"_";"-");"-";"_";1);"-";"";3)

Förklaringstexten till BYT.UT är lite förvirrande. "antal förekomster" skall nog vara "Förekomst NR".  Dvs 3:ean betyder att den tredje förekomsten byts ut, inte de tre första

Annars får du räkna ut hur många gånger det förekommer

=LÄNGD(A12)-LÄNGD(BYT.UT(A12;"_";""))-1

och klämma in där 3:an står

=BYT.UT(BYT.UT(BYT.UT(A12;"_";"-");"-";"_";1);"-";"";LÄNGD(A12)-LÄNGD(BYT.UT(A12;"_";""))-1)

 

VBA har mönstermatchning/regular expression. Då går det att trixa hur man vill (om du ändå skall köra makro).

hhttps://software-solutions-online.com/vba-regex-guide/

https://regexr.com/3hmb6

Men då måste kryssa i att VBA skall använde RegExp osv 

Du kan även använda VBAs egna sök/ersätt och utnyttja att det finns en inverteringsfunktion i VBA StrReverse

Sub tjo()
Dim mintext As String
Dim sistaStreck As Integer
mintext = Selection.Value
'byt ut alla "_" mot "-"
mintext = Replace(mintext, "_", "-")
'byt tillbasks det första "-" mot "_"
mintext = Replace(mintext, "-", "_", , Count:=1)
'Och byt tillbaks det sista "-" mot "" genom att vända på strängen och byta ut första förekomsten
mintext = Replace(StrReverse(mintext), "-", "", , Count:=1)
' och vänd tillbaks texten
mintext = StrReverse(mintext)

End Sub

 

eller utnyttja att VBA kan söka både framifrån och bakifrån

InStrRev function (Visual Basic for Applications) | Microsoft Docs

och göra något smart som jag inte kommer på...

 

För övrigt funkar inte notifiering hos mig. Jag fick bara notis om att du hade Messat.

[MH_]

Såg att jag missat en detalj

om det redan har bytts till det, byts ut till ingenting,

Om det inte finns mer än ett "_" så är det väl enklast att vända på makrot och börja med att byta ut det sista tecknet till "" (reverse) så är du färdig med den delen.

 

För formlerna blir det krångligare dey blir svårt om det saknas fler "_" än det första. Hmmm

Du skulle kanske kunna göra något smart med hjälp av

=OMFEL(SÖK("€";BYT.UT(A12;"_";"€";SEKVENS(10)));"")

som ger en matris med positionen för alla "_"

den här formeln som hittar det sista förekomsten av  "_"

=MAX(OMFEL(SÖK("€";BYT.UT(A12;"_";"€";SEKVENS(10)));""))

Men du måste nog krångla till det oavsett. 

VBA-Makrot låter enklare

[Mikael63]

"rådata" innehåller endast "_" och antalet kan variera. Det första "_" ska vara kvar och det sista ska tas bort. Alla där emellan ska ändras till "-".

VBA och markering av celler skulle vara perfekt. Då behöver jag inte klistra in formler i de Excelböcker som så att säga skapas av "källan". Koden kan då ligga i PERSONAL.XLSB

Jag får körfel 13 när jag testar tjo

 

[Mikael63]

Okej, nu har jag testat med formler igen. Nu i flera steg.

Rådata finns i A2
i B2 har jag =BYT.UT(BYT.UT(A2;"_";"-");"-";"_";1) lika tidigare
i C2 =VÄNSTER(B2;HITTA("#";BYT.UT(B2;"-";"#";LÄNGD(B2)-LÄNGD(BYT.UT(B2;"-";""))))-1)
i D2 =HÖGER(B2;LÄNGD(B2)-HITTA("#";BYT.UT(B2;"-";"#";LÄNGD(B2)-LÄNGD(BYT.UT(B2;"-";"")))))
och sedan i E2 har jag =SAMMANFOGA(C2;D2)

Troligen finns snyggare lösning men detta har jag hittat på olika ställen på nätet.
Detta fungerar men bara till en viss del märker jag när jag kör med skarpa värden. Jag får då #VÄRDEFEL! redan i C2 om det bara förekommer ett enda "_" vilket det tycks kunna göra. Efterföljande får ju då också värdefel

Det är också så, inser jag, att de med ett ett enda "_" ska det tecknet ersättas med ingenting...
Behöver kanske någon OM-sats då? Men var/hur?
(jag har lagt till en sådan i F2 eftersom jag får värdefel även på rader som inte innehåller något "_" alls, där ska textsträngen lämnas orörd)

[MH_]

=LET(n;LÄNGD(A1)-LÄNGD(BYT.UT(A1;"_";""));OM(n=0;A1;OM(n=1;BYT.UT(A1;"_";"");"formel som hanterar >1 _")))

Om 0 "_" hämta värdet från A1, Om 1 st "_" ersätt med "". Annars kör standardformeln

Kort och smidigt...

=LET(n;LÄNGD(A1)-LÄNGD(BYT.UT(A1;"_";""));OM(n=0;A1;OM(n=1;BYT.UT(A1;"_";"");BYT.UT(BYT.UT(BYT.UT(A1;"_";"-");"-";"_";1);"-";"";LÄNGD(A1)-LÄNGD(BYT.UT(A1;"_";""))-1))))

 

 

[Mikael63]

39 minuter sedan, MH_ sade:

Kort och smidigt...

Ja, verkligen 

Jag hade precis petat dit en extra kolumn med OMFEL (=OMFEL(F2;BYT.UT(A2;"_";"";1)))och fått det att lira men hellre då en kort och smidig formel!

Fungerar fint detta! Stort tack.

 

Ps. filen får nu heta Upprättning by MH_.xlsx
(Jag inser att det är enklare att kopiera in rådata till den filen än att kopiera in formeln och utöka serie i rådatafilen)

Edited June 30, 2022 by Mikael63
filnamn

[MH_]

Om det blir struligt kanske du kan testa med power Query. Det är väldigt smidigt när man skall rensa data och den sparade frågan blir dessutom "dokumenterad". 

Nackdelen är att det bli en  XXXX kod till man måste lära sig.