Plocka bort bokstäver?

[Mikael63]

Har en bok i Excel.

Boken har fem blad.

Cellerna i kolumn A (i varje blad) har ett innehåll som börjar på en eller två bokstäver åtföljda av tre till fem siffror. Ja det kan förekomma andra tecken efter dessa siffror.

 

Jag har möjlighet att nyttja kolumn D och uppåt i varje blad, jag har möjlighet att skapa och använda flera blad.

 

Det jag i slutänden vill ha är alla, från alla blad, dessa 3-5 siffror (+ev. andra tecken) med inledande bokstav/bokstäver bortplockade så att jag kan sortera dessa i nummerordning.

 

I varje blad kan man vilja peta in en ny rad och då ska sammanställningen uppdateras automatiskt. Ny sortering kan jag göra manuellt.

 

Eller - det jag behöver i slutändan är, egentligen, att kunna hitta ett ledigt sådant löpnummer, 3-5 siffror.

[Mikael63]

Det här är en variant:

=1*SAMMANFOGA(OMFEL(EXTEXT(A1;1;1)*1;"");OMFEL(EXTEXT(A1;2;1)*1;"");OMFEL(EXTEXT(A1;3;1)*1;"");OMFEL(EXTEXT(A1;4;1)*1;"");OMFEL(EXTEXT(A1;5;1)*1;"");OMFEL(EXTEXT(A1;6;1)*1;"");OMFEL(EXTEXT(A1;7;1)*1;""))

Tack, men jag får #VÄRDEFEL!

(även om jag prövar med en ny Bok)

Office 2010

 

Tillägg: Äh! EXTEXT skulle bytas mot MITT

[Mikael63]

Om man bara vill att inledande bokstäver ska tas bort?

 

Ex finns ett objekt som heter F106.1 och där vill jag att resultatet ska ge 106.1 för att inte hamna som 1061.

[Ove Söderlund]

En variant är att skapa en egendefinierad funktion med hjälp av lite VBA.

 

1. Starta VBA-editorn med kortkommandot ALT + F11

2. Infoga en modul från Infoga-menyn. Modulen kommer att finnas i din arbetsbok.

3. Lägg in följande kod:

Option Explicit

Function GetNumbersFromString(StringToSearch As Variant, NumbersOfChars As Integer) As Long

    Dim ShortString As String, ResultString As String
    Dim CountPos As Integer
    
    ShortString = Left(CStr(StringToSearch), NumbersOfChars)
    
    For CountPos = 1 To Len(ShortString)
    
        If IsNumeric(Mid(ShortString, CountPos, 1)) Then ResultString = ResultString & Mid(ShortString, CountPos, 1)
        
    Next CountPos
    
    GetNumbersFromString = CLng(ResultString)

End Function

4. Infoga formel i valfri cell i din arbetsbok med fx-knappen, välj från kategorin "Anpassade" och välj argumenten, text som ska sökas igenom och längd på texten (7 i ditt fall).

[Mikael63]

Punkt 4. Har ingen sådan kategori.

?

 

Och längd i mitt fall = 7 = ?
Jag vill bara filtrera bort första och andra bokstaven om där finns två, bara filtrera bort första bokstaven om där bara finns en.

 

Kanske bidrog jag med överflödig information när jag skrev:

 

åtföljda av tre till fem siffror. Ja det kan förekomma andra tecken efter dessa siffror.

..

[Ove Söderlund]

Hmm... det ser alltså inte ut som enligt denna skärmdump?

 

 

Om det är så att du saknar kategorin Anpassade som visas i dialogrutan så får du kolla så att du har lagt in koden i en modul i den arbetsbok där du kommer att använda funktionen. Det är ingen "global" funktion så den funkar endast i den arbetsbok du lagrat den i.

 

För att koden ska veta att det är just 3-5 siffror så utgår den från "värsta fallet" alltså två inledande bokstäver som följs av fem siffror, alltså 7 tecken som ska sökas igenom. Om det är t.ex. bara 4 tecken, A123 så kommer funktionen att bara returnera 123, dvs den fungerar även om det är kortare än 7 tecken som behöver sökas igenom, men den behöver söka igenom hela strängen eftersom koden inte kan veta om det är dels 1 eller 2 bokstäver och dels 3, 4 eller 5 siffror.

 

 

 

 

[Mikael63]

Ja, det är i samma bok

 

 

 

[Ove Söderlund]

Ja, det är i samma bok

 

 

Ok, som jag kan tyda det så har du lagt koden i "This workbook", men du ska infoga en modul och lägga in koden där, se bifogad skärmdump:

 

[Mikael63]

Ok, som jag kan tyda det så har du lagt koden i "This workbook", men du ska infoga en modul och lägga in koden där, se bifogad skärmdump:

 

Excel 2010 infoga anpassad funktion 2.jpg

AH! Ja, i en modul var det ja. men F106.1 ger fortfarande 1061

[Ove Söderlund]

Fick lägga till lite IF-satser, så här kommer en justerad kod:

Option Explicit

Function GetNumbersFromString(StringToSearch As Variant, NumbersOfChars As Integer) As Long

    Dim ShortString As String, ResultString As String
    Dim CountPos As Integer

    ShortString = Left(CStr(StringToSearch), NumbersOfChars)

    For CountPos = 1 To Len(ShortString)

        If IsNumeric(Mid(ShortString, CountPos, 1)) Then ResultString = ResultString & Mid(ShortString, CountPos, 1)

        If Len(ResultString) > 1 And IsNumeric(Mid(ShortString, CountPos, 1)) = False Then Exit For

        If Len(ResultString) = 3 Then Exit For

    Next CountPos

    GetNumbersFromString = CLng(ResultString)

End Function

[Mikael63]

Fungerar inte den förenklade varianten (kl 12:14)? 

Så här om den bara skall titta på de två första:

=SAMMANFOGA(OMFEL(EXTEXT(A1;1;1)*1;"");OMFEL(EXTEXT(A1;2;1)*1;"");EXTEXT(A1;3;99))

 

Annars kan du göra en matrisformel som hittar den första siffran och kopierar alla tecken från den och högerut.

{=HÖGER(A1;LÄNGD(A1)+1-MIN(OMFEL(SÖK(RAD($1:$10)-1;A1);"")))}

 

Båda dessa ger resultatet #NAMN?

 

OCH - jag ser gärna en lösning utan VBA då det inte är "min" fil och .xlsx är att föredra...

 

 

Bok1.xlsx

[Mikael63]

Fick lägga till lite IF-satser, så här kommer en justerad kod:

 

Ja, nu börjar det närma sig men ovanstående kod klippte ner till tre siffror.

Jag ändrade till 10 (bara för att) och använde även 10 i argumentet.

If Len(ResultString) = 10 Then Exit For

Nu klipps ex M12117.1 så att resultatet blir 12117 och det är exakt vad jag behöver.

 

Okej - denna kod då utökat neråt, i varje blad.

Sen skulle jag då sammanställa alla dessa till en kolumn i ett nytt Blad (för att sortera).

Enklast?

 

Tillägg/stopp!

Jag tror jag krånglar till det i onödan!

Sammanställningen behöver/ska inte visa alla värden från alla blad tillsammans utan var för sig.

Jag kan då bara skapa en kolumn, i mitt extrablad, för vardera av de övriga bladen och bara låta cellerna matcha rakt av och sedan sortera dessa kolumner individuellt, genom att markera bara en kolumn åt gången.

[Mikael63]

Fungerar inte den förenklade varianten (kl 12:14)? 

Så här om den bara skall titta på de två första:

=SAMMANFOGA(OMFEL(EXTEXT(A1;1;1)*1;"");OMFEL(EXTEXT(A1;2;1)*1;"");EXTEXT(A1;3;99))

 

Annars kan du göra en matrisformel som hittar den första siffran och kopierar alla tecken från den och högerut.

{=HÖGER(A1;LÄNGD(A1)+1-MIN(OMFEL(SÖK(RAD($1:$10)-1;A1);"")))}

 

Båda dessa fungerar utmärkt hemma med Libre Office Calc.

Varför funkar inte dessa i Office 2010 då tro?

[Mikael63]

Båda dessa fungerar utmärkt hemma med Libre Office Calc.

Varför funkar inte dessa i Office 2010 då tro?

 

Okej, även här ska EXTEXT bytas mot MITT

=SAMMANFOGA(OMFEL(MITT(A1;1;1)*1;"");OMFEL(MITT(A1;2;1)*1;"");MITT(A1;3;99))

Här beskrivs EXTEXT:

https://support.office.com/sv-se/article/Funktionen-EXTEXT-230833b1-28e7-4ef8-9286-eba06a3c29ad

Men i de exempel som visas står det MITT

 

Vilket stämmer med:

http://addisco.se/omdopta-funktioner-excel2010/

 

Båda ska ha ett STORT tack för hjälp

[Monshi]

Ja det var några förbryllande veckor när inga funktionsnamn stämde...

 

Får jag lov att påpeka att det är en bra idé att putta in de uppdateringar som MS släpper. De förbättrar funktion och ökar säkerheten i programmen.

[Mikael63]

Ja det var några förbryllande veckor när inga funktionsnamn stämde...

 

Får jag lov att påpeka att det är en bra idé att putta in de uppdateringar som MS släpper. De förbättrar funktion och ökar säkerheten i programmen.

 

Ja, det är nåt för IT-avdelningen att tänka på, vet inte varför det inte redan är gjort faktiskt..