Avatar billede lotte48 Nybegynder
12. marts 2006 - 21:03 Der 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.

Hvordan skiller man det ad ??

/Lotte
Avatar billede excelent Ekspert
12. marts 2006 - 21:27 #1
hvis der er mellemrum mellem by og postnr.

=HØJRE(A3;FIND(" ";A3)-1) ' post nr
=VENSTRE(A3;LÆNGDE(B3)) ' by
Avatar billede excelent Ekspert
12. marts 2006 - 21:40 #2
rettelse by
=VENSTRE(A3;FIND(" ";A3))
Avatar billede excelent Ekspert
12. marts 2006 - 21:50 #3
rettelse postnr.
=HØJRE(A3;LÆNGDE(A3)-FIND(" ";A3))
Avatar billede Slettet bruger
12. marts 2006 - 21:59 #4
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.
Avatar billede excelent Ekspert
12. marts 2006 - 22:37 #5
eller marker navne+postnr
vælg 'tekst til kolonne' i menuen data
vælg afgrænset
klik næste
vælg mellemrum
klik næste
klik udfør
Avatar billede excelent Ekspert
13. marts 2006 - 00:22 #6
Postnr og bynavn i kolonne A
fx.
8600 Silkeborg

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;))
Avatar billede excelent Ekspert
13. marts 2006 - 00:36 #7
A2=8600 Silkeborg
B2=VENSTRE(A2;MAKS(HVIS(ER.TAL(--MIDT(A2;RÆKKE(INDIREKTE("1:100"));1));RÆKKE(INDIREKTE("1:100")))))
C2=FJERN.OVERFLØDIGE.BLANKE(UDSKIFT(A2;B2;))
Avatar billede mrjh Novice
13. marts 2006 - 08:46 #8
Hvis din tekst står i K2:
Postnr.: =VÆRDI(VENSTRE(K2;FIND(" ";K2)-1))
By: =MIDT(K2;FIND(" ";K2)+1;100)
Avatar billede Slettet bruger
13. marts 2006 - 20:12 #9
excelent: Jeg er lykkelig. Deen funktion kendte jeg heller ikke. Tusind Tak.
Avatar billede excelent Ekspert
13. marts 2006 - 21:17 #10
lol tak.
skal jeg være ærlig, så syntes jeg faktisk mrjh's er bedre
godt tænkt mrjh :-)
Avatar billede lotte48 Nybegynder
14. marts 2006 - 20:14 #11
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Ö.

Endnu en variant:  1801-001 LISBON

/Lotte
Avatar billede lotte48 Nybegynder
14. marts 2006 - 20:30 #12
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

A2=8600 Silkeborg
B2=VENSTRE(A2;MAKS(HVIS(ER.TAL(--MIDT(A2;RÆKKE(INDIREKTE("1:100"));1));RÆKKE(INDIREKTE("1:100")))))
C2=FJERN.OVERFLØDIGE.BLANKE(UDSKIFT(A2;B2;))

Men.....  hvad står de enkelte kommandoer for ???  Og hvorfor skal det være en matrix-formel for at virke

/Lotte
Avatar billede excelent Ekspert
14. marts 2006 - 21:09 #13
hej lotte.

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.
Avatar billede lotte48 Nybegynder
14. marts 2006 - 21:50 #14
-> excelent

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.

/Lotte
Avatar billede excelent Ekspert
14. marts 2006 - 22:25 #15
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
Avatar billede mrjh Novice
14. marts 2006 - 22:45 #16
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 :-)
Avatar billede excelent Ekspert
14. marts 2006 - 22:50 #17
jo kortere jo bedre efter min mening
må vi se mrjh
Avatar billede mrjh Novice
14. marts 2006 - 23:01 #18
Den er såmeænd ikke meget kortere, andet end at jeg har fjernet indirekte("")
Avatar billede mrjh Novice
15. marts 2006 - 21:30 #19
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 :-)
Avatar billede lotte48 Nybegynder
15. marts 2006 - 22:25 #20
->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.

/Lotte
Avatar billede mrjh Novice
16. marts 2006 - 08:33 #21
-> 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 :-)
Avatar billede excelent Ekspert
16. marts 2006 - 09:33 #22
Jeg surfede lidt i går, men kunne ikke finde siden igen.
men det var en engelsksproget side.
Avatar billede excelent Ekspert
16. marts 2006 - 21:28 #23
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
Avatar billede excelent Ekspert
18. marts 2006 - 11:34 #24
eller denne version (tildel makro en genvejstast)

Sub Makro2()

'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

End Sub
Avatar billede excelent Ekspert
18. marts 2006 - 11:54 #25
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.
Avatar billede mrjh Novice
21. marts 2006 - 12:48 #26
Husk at lukke spm. ved at markere navn og accptere
Avatar billede excelent Ekspert
26. marts 2006 - 09:20 #27
svar
Avatar billede excelent Ekspert
27. marts 2006 - 05:56 #28
ellers læg selv et svar, så andre ikke spilder tid på spørgsmål der er besvaret
Avatar billede lotte48 Nybegynder
29. marts 2006 - 10:57 #29
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
Avatar billede excelent Ekspert
29. marts 2006 - 19:08 #30
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

ActiveCell.Offset(0, 1).Value = Left(txt, tt)
ActiveCell.Offset(0, 2).Value = Mid(txt, tt + 2, x - tt)
ActiveCell.Offset(1, 0).Select

End Sub
Avatar billede mrjh Novice
29. marts 2006 - 19:55 #31
Excelent, prøv ' tegnet foran left sætningen (formatterer til tekstværi)
"'" & Left(txt, tt)
Avatar billede excelent Ekspert
29. marts 2006 - 20:50 #32
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. :-)
Avatar billede mrjh Novice
29. marts 2006 - 21:26 #33
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
Avatar billede mrjh Novice
15. april 2006 - 15:17 #34
Lukketid ?
Avatar billede lotte48 Nybegynder
16. april 2006 - 22:16 #35
Tak for hjælpen

/Lotte
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