Excel formattering - Hvordan fikse dette?

[DeTomaso]

Hei.

 

Jeg har en excelfil med endel rader som jeg ønsker endret noe på.

 

Den ene raden inneholder personnummer. Her ønsker jeg å fjerne de siste fem tegnene og erstatte disse med *****.

12121212345 -> 121212*****

 

 

Så har jeg en datorad hvor datoen er skrevet på denne måten: 20090425.

Jeg ønsker 25.04.09... Er det mulig å endre dette på en grei måte??

 

Håper det er noen som kan hjelpe meg med dette.

På forhånd tusen takk :-)

[DeTomaso]

Den ene raden inneholder personnummer. Her ønsker jeg å fjerne de siste fem tegnene og erstatte disse med *****.

12121212345 -> 121212*****

 

OK: Jeg fant ut at det er mulig å benytte formelen =VENSTRE(A1,6) for å få ut bare de seks første tegnene men jeg finner ikke ut hvordan jeg legger på ***** på slutten av dette tallet...

 

Håper noen kan bistå :-)

[StoreMyen]

Som det meste i Excel finnes det fler måter å gjøre dette på

Her er min:

 

Xcel_exmpl.bmp

12121212345 =LEFT(A1;6)&"*****"

Dette gir

12121212345 121212*****

 

20090425 =RIGHT(A5;2)&"."&LEFT(RIGHT(A5;4);2)&"."&LEFT(RIGHT(A5;6);2)

Dette gir

20090425 25.04.09

 

 

Jeg får imidlertid ikke Excel til automatisk å skjønne at det har med datoer å gjøre.

Ser ikke bort fra at andre har en mer elegant metode for denne delen.

Endret 6. mai 2009 av SamBell

[HiSpeed]

Du kan også bruke

 

=KJEDE.SAMMEN(VENSTRE(<celle med f.nr.>;6); GJENTA("*****";1);)

Da får du: 121212***** som resultat.

Endret 6. mai 2009 av HiSpeed

[DeTomaso]

Som det meste i Excel finnes det fler måter å gjøre dette på

Her er min:

 

Xcel_exmpl.bmp

12121212345 =LEFT(A1;6)&"*****"

Dette gir

12121212345 121212*****

 

20090425 =RIGHT(A5;2)&"."&LEFT(RIGHT(A5;4);2)&"."&LEFT(RIGHT(A5;6);2)

Dette gir

20090425 25.04.09

 

 

Jeg får imidlertid ikke Excel til automatisk å skjønne at det har med datoer å gjøre.

Ser ikke bort fra at andre har en mer elegant metode for denne delen.

 

Hei SamBell.

 

Takk for kjapt svar!! :-)

 

Det funker veldig bra!

 

Kan jeg lage dette som en makro slik at når jeg får en slik fil med slikt innhold så kan jeg kjøre makroen istedet for å sette inn ny kolonne til høyre for fnr. kolonnen for så å sette inn formelen?

 

Håper noen har en ide om dette med hvordan jeg kan få endret datoformatet :-)

[StoreMyen]

Det kan du sikkert, men jeg er ikke spesielt god på macroer.

En enklere metode er å dumpe rådata inn i et Excel-ark, for så å hente verdiene frem til et ark som kan presenteres. Skal dette sendes videre tar en gangske enkelt å kopierer hele siden og limer inn verdier og formater (ikke formler)

 

Dette passer selvfølgelig best om en får dataen på samme format hver gang.

En må i tillegg passe på at en får med alle linjer.

 

Om du får data fra et annet system bør en på sikt få gjort noe med formateringen av rapporten, men det vet vi av erfaring at ikke alltid er like enkelt :-)

 

Sam

[DeTomaso]

Det kan du sikkert, men jeg er ikke spesielt god på macroer.

En enklere metode er å dumpe rådata inn i et Excel-ark, for så å hente verdiene frem til et ark som kan presenteres. Skal dette sendes videre tar en gangske enkelt å kopierer hele siden og limer inn verdier og formater (ikke formler)

 

Dette passer selvfølgelig best om en får dataen på samme format hver gang.

En må i tillegg passe på at en får med alle linjer.

 

Om du får data fra et annet system bør en på sikt få gjort noe med formateringen av rapporten, men det vet vi av erfaring at ikke alltid er like enkelt :-)

 

Sam

 

OK.

 

Jeg får nok ikke dataene levert på annen måte desverre.

 

Jeg har støtt på et annet problem også... Det viser seg at dersom fødselsnummeret starter med 0 så fjernes denne...

Dermed blir "=Venstre" formelen feil. Her må jeg vel lage en hvis-setning som sjekker om det er 10 eller 11 siffer og hvor det ved 10 siffer settes inn en 0 først i setningen?

 

Noen ide om hvordan jeg kan gjøre dette sammen med denne formelen =VENSTREA1;6)&"*****" ?

[StoreMyen]

Prøv

Format Cell

Special

Personnummer

 

Da skal nullen legges til automatisk.

 

Eventuelt:

Format Cell

Custom

000000-00000 eller 00000000000

 

Det skal gi samme resultat

 

[EDIT]

Ser at dette ikke fungerer på *****.

Dermed må du enten formatere som text før du klipper inn nummerene eller sette ' fremfor de orginale nummerene, slik at Excel oppfatter dem som tekst og teller dem med.

 

 

Sam

Endret 6. mai 2009 av SamBell

[DeTomaso]

Hei igjen...

 

Ser at det blir noe vanskelig, men kan jeg si at jeg vi ha bort de 5 siste tegnene istedet for at jeg vil bevare de 6 første?

det vil jo medføre at et personnummer som starer med 0 og hvor jeg bare har 10 siffer, vil returnere f.eks 42509 istedet for 042509...

 

Har prøvd meg med =HØYRE men det ble ikke helt bra...

[StoreMyen]

Sorry kan ikke hjelpe deg der.

Mitt forslag blir at du formaterer som text før du klipper inn. Da vil nullen bil værende.

 

Sam

[Harald Staff]

Hei

 

Med personnummer som tall (ikke tekst) i A1. Tall er høyrejustert og fjerner 0 først i tallet.

 

Forstår det slik at du bruker norsk excel.

 

Fødselsdato:

=DATO(100*(REST(HELTALL(A1/100000);100)<10)+1900+REST(HELTALL(A1/100000);100);REST(HELTALL(A1/10000000);100);REST(HELTALL(A1/1000000000);100))

 

Maskert personnummer:

=TEKST(HELTALL(A1/100000);"000000")&"*****"

 

Videre med datorepresentasjon 20090425 i celle A3. Exceldato:

=DATO(HELTALL(A3/10000);REST(HELTALL(A3/100);100);REST(A3;100))

 

(Fjern eventuelle linjeskift hvis du limer inn disse formlene i formelfeltet.)

 

HTH. Beste hilsen Harald

[DeTomaso]

Hei

 

Med personnummer som tall (ikke tekst) i A1. Tall er høyrejustert og fjerner 0 først i tallet.

 

Forstår det slik at du bruker norsk excel.

 

Fødselsdato:

=DATO(100*(REST(HELTALL(A1/100000);100)<10)+1900+REST(HELTALL(A1/100000);100);REST(HELTALL(A1/10000000);100);REST(HELTALL(A1/1000000000);100))

 

Maskert personnummer:

=TEKST(HELTALL(A1/100000);"000000")&"*****"

 

Videre med datorepresentasjon 20090425 i celle A3. Exceldato:

=DATO(HELTALL(A3/10000);REST(HELTALL(A3/100);100);REST(A3;100))

 

(Fjern eventuelle linjeskift hvis du limer inn disse formlene i formelfeltet.)

 

HTH. Beste hilsen Harald

 

Helt genialt!!! Tusen tusen takk!!

 

Disse formlene fungerer utmerket Harald.

 

Med fare for å være storforlangende: har du en ide om hvordan jeg kan benytte en makro for sette inn disse formlene i et nytt regneark som har de samme formatteringene? Eller blir det slik at jeg må sette inn disse formlene hver gang jeg mottar denne filen?

 

Nok en gang tusen takk for genial hjelp!

[StoreMyen]

Som nevnt før er bruken av to ark i den samme arbeidsboken det enkleste.

[Harald Staff]

Med fare for å være storforlangende: har du en ide om hvordan jeg kan benytte en makro for sette inn disse formlene i et nytt regneark som har de samme formatteringene? Eller blir det slik at jeg må sette inn disse formlene hver gang jeg mottar denne filen?

 

Full av ideer. Men for en "working demo" må jeg vite veldig mye mer om hva som står hvor hvorfor og andre tilsynelatene unødvendige detaljer. Og hva dette skal bli til slutt. F.eks. hvis du skal gi fila til andre personer, så må personnummer etc fjernes helt fra den, ikke bare skjules. Man kan ikke gjemme informasjon i Excelfiler.

 

Hvis du spiller inn en makro mens du utfører dette manuelt, er du et veldig godt stykke på vei. Det blir overdimensjonert grisete kode, men gjør jobben på et par sekunder allikevel. Så kan vi justere den til derfra hvis du støter på problemer.

 

Bra det funka

 

Beste hilsen Harald

 

Edit PS: Det står <10 i en formel. Den gjør sånn at hvis man er født i år 09 eller lavere, så er det i 2009,ikke 1909. Er man født i 11 så er det derimot 1911. Du bør endre 10 til et høyere tall dersom du skal bruke denne i flere år. Vet ikke hvor gamle folk du dealer med, men er det under pensjonsalder, endre i formelen til <39 så har du for en stund.

Endret 6. mai 2009 av Harald Staff

[ErlendF]

Hei

Jeg har laget denne formelen som et alternativ. Den skal hente ut fødselsdato fra personnummeret, den ble ganske lang men ser ut til å fungere, den tar høyde for personnummer som starter på 0, og funker på alle under 100 år, og lar feltet være tomt ved feil (formelen har personnummer i E kolonnen.

=HVISFEIL(DATO(HVIS(VERDI(DELTEKST(HØYRE(E2;7);1;2))>(ÅR(IDAG())-2000);DELTEKST(HØYRE(E2;7);1;2);((DELTEKST(HØYRE(E2;7);1;2)+100)));DELTEKST(HØYRE(E2;9);1;2);HVIS(LENGDE(E2)=11;DELTEKST(HØYRE(E2;11);1;2);DELTEKST(HØYRE(E2;10);1;1)));"")

[Radford]

Wow, 2009...