12. marts 2006 - 21:03Der er
33 kommentarer og 2 løsninger
Dele kolonne i to
Jeg har et udtræk af en database, hvor Postnr og by står i samme celle. Jeg vil gerne have delt denne kolonne, så postnummer står i en kolonne og bynavn i næste kolonne. Problemet er, at der er udenlandske postnumre iblandt, så nummeret kan have variabel antal tegn.
Nu skal du bare høre - for det lyder meget indviklet, men det virker. Indsæt to kolonner i dit excelark. Du skal bruge dem om lidt. Marker den kolonne, du vil ændre og kopier den over i et word-dokument. Jo, du læste rigtigt. Tænd for vis/skjul (golfkøllen i værktøjslinien), så du kan se, hvad du laver Marker postnummer/by i word og og klik på tabel - konverter - tabel til tekst. Nu kommer det sjove: Marker det hele igen: Rediger-erstat. Fanen Erstat. Søg efter: Her laver du et afsnitstegn (bare et tryk på mellemrumstasten) Erstat med: Ned i bunden til knappen Speciel. Her vælger du tabulatortegn - det ser sådan ud ^t Klik på Erstat alle Nu har du en række postnumre og en række byer med tabulator imellem. Marker det hele igen og sæt det ind i excel. Vips: to kolonner HUSK de skal være i celle 2. Der skal jo stå en kolonneoverskrift i celle 1 Gør det samme med den anden word-kolonne. Luk og sluk efter dig i word. Jeg har aldrig kunnet finde ud af det der med at fjerne x antal tegn, men nogen må gerne gi' et kursus.
denne udskiller postnr.: formel afsluttes med CTRL+SHIFT+ENTER {} =VENSTRE(A2;MAKS(HVIS(ER.TAL(--MIDT(A2;RÆKKE(INDIREKTE("1:100"));1));RÆKKE(INDIREKTE("1:100")))))
denne udskiller bynavn.: formel afsluttes normal -blot ENTER =FJERN.OVERFLØDIGE.BLANKE(UDSKIFT(A2;B2;))
Herligt med alle de alternativer.... Glemte jo lige at skrive, at et udenlandsk nummer kan være f.eks. N-0902 Oslo.
Når der er et bogstav i postnummeret får jeg #Værdi Hvis jeg fjerner N- og sætter en gnyf foran '0902 så får jeg alligevel kun 902 med i nummerfeltet ?????
Og hvis formatet er skrevet sådan S-212 39 MALMÖ, så kommer Byen til at hedde 39 MALMÖ.
Jeg fandt svaret ved at fortsætte med at prøve formlerne. Jeg var jo også vældig imponeret over den korte form, som mrjh kunne foreslå - og jeg vil bestemt bruge den til andre formål. Bl.a. fordi jeg kan gennemskue, hvad der sker.
Denne - excelent's virker i alle tilfælde.... når blot jeg husker at slutte redegering med Ctrl-Shift-Enter
Vedr. de forskellige kommandoer, så er det nemmeste (specielt for mig) :-) at slå dem op i Excel-hjælpe filen. Jeg vil dog lige nævne en', som jeg selv havde lidt problemer med at forstå
......RÆKKE(INDIREKTE("1:100")).................
Hvis du prøver at ændre 100 til fx. 2, vil du se, at du kun får 2 tal med i postnummer,- så 100 er jo nok lidt overdrevet, men så klarer den jo også tekststrenge på op til 100 tegn, hvis det nogensinde bliver aktuelt. Så i princippet kunne du sagtens ændre det til 8-10 stykker uden problemer.
Jeg kan sagtens slå op... og jeg kan også skrive af... men jeg kan ikke finde fornuften eller forklaringen på, at der skal to minus'er foran MIDT og heller noget om, hvorfor man vælger RÆKKE(INDIREKTE("1:100")) i denne sammenhæng.
nå ok skulle være sådan =VENSTRE(A2;MAKS(HVIS(ER.TAL(-MIDT(A2;RÆKKE(INDIREKTE("1:100"));1));RÆKKE(INDIREKTE("1:100"))))) men '---' virker på samme måde =VENSTRE(A2;MAKS(HVIS(ER.TAL(---MIDT(A2;RÆKKE(INDIREKTE("1:100"));1));RÆKKE(INDIREKTE("1:100"))))) ligesåvel som 1++2=3 men hvorfor Excel accepterer det aner jeg ikke
Ja jeg kan heller ikke rigtig hitte ud af hvordan den fungerer, men det er dælme smart tænkt excelent. Har dog fundet ud af at man kan fjerne indirekte og bare lade den tælle i værdierne 1:100, så er forvirringen måske en lille smule mindre :-)
Den er temmelig anvendelig denne formel og kan bruges til mange ting. specielt Række(1:100) som kan buges i mange forskellige sammenhænge til at analysere og beregne på de enkelte tegn inde i cellerne. Minusset foran midt omdanner bare udtrykket til tal-værdier og fungerer på samme måde som værdi(). Indirekte kan ikke fjernes fra formlen idet den "fastfryser" referencen 1:100. Jeg synes lige jeg ville involvere jer i mine opdagelser af denne fantastiske formel :-)
->mrjh Tak for dine hints - de hjælper lidt på forståelsen, jeg har da også fået lidt inspiraton til at søge videre på nettet efter forklaringer. Nødvendige for mig, hvis jeg skal komme i tanker om at bruge sådanne formler tíl andre formål.
Jeg fik løst det aktuelle problem, så jeg siger TAK for jeres hjælp og beder dig om at lægge et svar til point-fordelingen.
-> Lotte48 Ellers tak, men det er excelent som har fortjent P., hvorend han så har fundet/fundet ud af det. Jeg har sådan set bare brugt spm. til at udtrykke min udelte begejstring over udtrykket som virkelig har givet stof til eftertanke :-)
hej jamen så er her en sub som gør det samme som funktionen kan måske være til ydeligere inspiration
Sub Makro1() 'Hvis Postnr. og bynavn er placeret i celle B3 'så placeres postnr. i Celle C3 og Bynavn i Celle D3 Dim i, x, tal Dim txt As String Dim t(100) Dim tt Dim post
txt = [b3].Value x = Len(txt)
For i = 1 To x t(i) = Val(Mid(txt, i, 1)) If t(i) > 0 Then tal = tal + 1: tt = i Next post = Mid(txt, 1, tt) If Len(post) > tal Then [c3].Value = Mid(txt, 1, tt) Else [c3].Value = Val(txt) End If [d3].Activate ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-2],RC[-1],)" End Sub
'Marker celle med postnr. og bynavn der skal deles, 'aktiver genejstast/makro ind til aktuelle celler er delt
Dim tal, post, x, tt, i As Double Dim txt As String: Dim txt2 As String Dim t(100): Dim msg As String txt = ActiveCell.Value: txt2 = txt: x = Len(txt) If txt = "" Then ActiveCell.Offset(1, 0).Select: End For i = 1 To x t(i) = Val(Mid(txt, i, 1)) If t(i) > 0 Then tal = tal + 1: tt = i Next If tt > tal Then ActiveCell.Offset(0, 1).Value = Mid(txt, 1, tt) Else ActiveCell.Offset(0, 1).Value = Val(txt) End If msg = ActiveCell.Offset(0, 1).Value ActiveCell.Offset(0, 2).Select txt2 = Application.WorksheetFunction.Substitute(txt, msg, "") txt2 = Application.WorksheetFunction.Trim(txt2) ActiveCell.Value = txt2 ActiveCell.Offset(1, -2).Select
Med Makro2 behøver du ikke have post+by i en bestemt kolonne du vælger/markerer blot den første celle i den kolonne de er placeret i, og aktiverer din genvejstast til makroen.
Excelent-> Jeg ville gerne bruge din VBA macro (2), men jeg kan ikke få den til at fungere korrekt - og jeg vil gerne bruge den på hele den del af kolonnen, som indeholder data - i stedet for en enkelt celle?? (Jeg giver gerne flere points, hvis du mener at spørgsmålet er udvidet)
Jeg lægger lige en liste, som viser hvilke numre, der kikser. Fælles er bl.a., nullernes positioner... men det er ikke hele sandheden.
0900 København C 9 000 København C (??? bytter om) 1000 København K 1000 København K 1090 København K 109 0 København K 3080 Tikøb 308 0 Tikøb 1253 København K 1253 København K (OK) CH-86100 Uster CH-861 00 Uster S-16 103 BROMMA S-16 103 BROMMA (OK) IS-200 Kopavogur IS-2 00 Kopavogur PL-59 500 ZLOTORYJA PL-59 5 00 ZLOTORYJA
Ja det er godt nok nogen drilske postnumre du har fundet frem :-) prøv lige denne reviderede udgave. Hvis du knytter en genvejstast til makroen, så er det blot at markere det øverste post-bynavn, og så aktivere genvejstasten ind til alle celler du ønsker delt er delt. (det er lige så hurtig som at markere området og så køre makro.) Jeg har problemer med dette nr. 0900 København C (= 900 København C) Skal se om jeg kan finde en løsning. Hvis du skal have hjælp til at sætte makro ind, el tildeling af genvejstast, så sig til.
Sub DelPostBy()
'Marker celle med postnr. og bynavn der skal deles, Kør makro 'Derefter placerer postnr. og by i kolonnerne til højre for valgt celle Dim tal, x, tt, i As Double Dim txt As String txt = ActiveCell.Value If txt = "" Then ActiveCell.Offset(1, 0).Select: End x = Len(txt)
For i = 1 To x If IsNumeric(Mid(txt, i, 1)) Then tal = tal + 1: tt = i Next
ja du siger noget mrjh, postnr formateres godt nok til tekst, men det betyder vel ikke noget. så med mindre der findes en anden løsning, må den kunne gå an. :-)
Excelents fungerer perfekt. Supergodt gået excelent. :-) Her er en alternativ som tager alle indtastninger i kolonne A og splitter dem op i Kol. B & C. Jeg synes lige jeg ville bringe den efter at have brugt 3 timer på den.
Sub PostnrBy() Dim a As Range, b As Range, c, d, e, Længde For Each b In Range("a1", Range("a60000").End(xlUp)) e = b.Value For Each a In b Længde = a.Characters.Count For i = 1 To a.Characters.Count If IsNumeric(a.Characters(i, 1).Text) Then c = c + 1 d = i End If Next Next b.Offset(0, 1) = "'" & Left(e, d) b.Offset(0, 2) = Mid(e, d + 2, Længde) Next End Sub
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.
Slettet bruger
Ny bruger
Nybegynder
Opret
Preview
