Format integer naar tekst?

[tshw3]

Goedemorgen Helpmij leden,

Ik loop weer tegen - waarvan ik denk - een simpel probleem aan.
Maar zit er al een aardig tijdje mee te stoeien en ik kom er niet uit.

Ik heb een excel sheet, waarbij ik waardes kan plakken in kolom A, met als doel om tussen alle waardes pipetekens te plaatsen.
Dit werkt allemaal naar behoren.

Nu heb ik ook een knop "snel nummers creëren" waarbij je dus opgeeft hoeveel nummers je in pipetekens wilt hebben.
Ook dit gaat goed en wordt juist geformatteerd.

Stel ik wil 5 opvolgende nummers dan krijg je:
001
002
003
004
005

In kolom A en met Pipetekens ziet het er dan zo uit:
001|002|003|004|005

echter wordt dit niet gedaan en staat er:
1|2|3|4|5

Dit komt doordat de opmaak wel 001 etc. laat zien maar in het veld daadwerkelijk 1/2/3 staat.

Ik heb al van alles geprobeerd met;

'Text = Format(Range("A2"), "000")
Text = Application.WorksheetFunction.Text(Range("A2"), "0.00")
 .ActiveCell.NumberFormat = "@"

Maar werkt allemaal niet.

Wie heeft de gouden tip?
Bekijk bijlage Test bestand.xlsm

 

[AHulpje]

Right("000" & sn(j, 1), 3)

 

[tshw3]

Right("000" & sn(j, 1), 3)

Goedemorgen AHulpje,

Nu pakt het toch gewoon de 3 rechterkarakters?
Voor mij begrijp ik dit niet helemaal, moet ik dit in vba plakken? Hoe krijg ik het werkend voor heel kolom A:A?
Of moet ik dit in de formule plakken die ik al heb?

 

[AHulpje]

Op alle plaatsen waar je de voorloopnullen wilt zien.
In Sub maak_pipetekens() bijvoorbeeld:

    PipeFile.Write Right("00" & sn(1, 1), 3)
    For j = 2 To UBound(sn)
        PipeFile.Write "|" & Right("00" & sn(j, 1), 3)
    Next

 

[tshw3]

Op alle plaatsen waar je de voorloopnullen wilt zien.
In Sub maak_pipetekens() bijvoorbeeld:

Dit is nog niet helemaal de oplossing, voor alles werkt het behalve voor het creëren van nummers.
Ik zou graag dat de VBA maakt voor Sub creeër_snel_nummers()
001
002
003

etc. aanmaakt.

Dus bijv. eerst 1 tot en met 3 en vervolgens ook daadwerkelijk formatteert naar bovenstaand.
of meteen goed zet in platte tekst maar dan opvolgend.

 

[snb]

Ik word wel nieuwsgierig waarvoor je dit gaat gebruiken ?

Sub M_snb()
  Cells(1) = Join([transpose(text(row(1:20),"000"))], "|")
End Sub

in een bestand:

CreateObject("scripting.filesystemobject").CreateTextFile(Application.DefaultFilePath & "pipetekensbestand.txt").Write Join([transpose(text(row(1:20),"000"))], "|")

 

[edmoor]

Doe het in de macro die al achter je knopje zit:

For i = 1 To number
    Range("A1").Cells(i, 1) = [COLOR="#FF0000"]"'" & Format(i, "00#")[/COLOR]
Next i

 

[snb]

@Edm

Teveel interaktie met het werkblad.

 

[edmoor]

Die code zit al in z'n document.
Alleen de i even vervangen door het rode gedeelte.

 

[AHulpje]

Een heel praktische oplossing van Edmoor, op één plek aanpassen en je bent overal vanaf.
En om snb een plezier te doen, het kan nog ietsje korter:

= Format(i, "'00#")

 

[lam201]

Nog een oplossing dan.

in je sub creeër_snel_nummers() zou ik in plaats van een loop (i=1 to number) deze gebruiken:

Range("A1:A" & number).Formula = "=text(row(),""000"")"
Range("A1:A" & number).Value = Range("A1:A" & number).Value

en voor de formule in cel B9

=TEXTJOIN("|";TRUE;TEXT(SEQUENCE(COUNTA(A:A));"000"))

of gewoon zo, zonder macro code:

=TEXTJOIN("|";TRUE;TEXT(SEQUENCE(B24);"000"))

met in cel B24 het aantal gewenste nummers

 

[snb]

Omdat het resultaat in 1 bestand moet komen zijn alle ' overbodig; format zorgt voor een tekenreeks, de pipeline zorgt ervoor dat Excel zich koesthoudt.
Cijfers in afzonderlijke cellen is in dit geval slechts een (overbodige) tussenstap.
Dat geldt ook voor de samenvoeging van cellen in 1 cel.
Het doel is een tekstbestand met pipelingescheiden cijfers. Excel doet er niet toe.
Zie mijn 2e codesuggestie.

 

[tshw3]

Dank voor alle hulp!

Allereerst dank voor alle reacties, de oplossing van Edmoor werkt inderdaad perfect met mijn bestaande oplossingen.
De antwoorden van Lam201/Ahulpje en SNB zal ik dit weekend even rustig naar kijken.

Toelichting waarom ik dit zou willen, voor mijn werk wordt veel gebruik gemaakt van Microsoft Business Central, door middel van pipetekens bouw je heel snel een filter.
Dit filter kan je weer gebruiken op bijvoorbeeld klantartikelen, klantnrs maar ook bijvoorbeeld pallet nummers etc.

Dus vandaar dat dit een mooie oplossing is.

Dank daarvoor!

 

[tshw3]

Omdat het resultaat in 1 cel moet komen zijn alle ' overbodig; format zorgt voor een tekenreeks, de pipeline zorgt ervoor dat Excel zich koesthoudt.
Cijfers in afzonderlijke cellen is in dit geval slechts een (overbodige) tussenstap.

Als toevoeging, je hebt gelijkt als het gaat om nummers creëren.
Maar het komt ook regelmatig voor dat ik in een andere excel al een reeks heb van bijvoorbeeld 40 waardes in één kolom. Deze kan ik dan makkelijk plakken om een filter te creëren.

 

[snb]

Dan doen we het nog eenvoudiger

Sub M_snb()
  range("A1:A40").copy
  With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .GetFromClipboard
    CreateObject("scripting.filesystemobject").CreateTextFile(Application.DefaultFilePath & "pipetekensbestand.txt").Write replace(.GetText,vbtab,"|")
  End With
End Sub

 

[edmoor]

Als je het vaker nodig hebt is een UDF misschien wel handig.

=Teun_Samenvoegen(A1:A5;"|";0)

Die 0 op het einde geeft aan dat je de waarde van een cel wilt hebben.
Als je er een 1 van maakt geeft het aan dat je de opmaak wilt hebben.

Public Function Teun_Samenvoegen(Target As Range, Delimiter As String, ValOrText As Integer)
    Dim Str() As String
    Dim cl As Range
    Dim i As Long
    
    For Each cl In Target
        ReDim Preserve Str(i)
        Str(i) = IIf(ValOrText = 0, cl.Value, cl.Text)
        i = i + 1
    Next cl
    Teun_Samenvoegen = Join(Str, Delimiter)
End Function

In je voorbeeld geeft deze met een 0 dit:
1|2|3|4|5

En met een 1 dit:
001|002|003|004|005

 

[tshw3]

Dank voor de toegevoegde reacties, ik denk dat ik er intussen al ben.
Omdat er ook lege waardes in kunnen staan etc. dat had ik werkend met mijn huidige macro's.

Met de uitbreiding van huidige formules, weet ik zeker dat het e.e.a. goed blijft gaan.

Met name de laatste oplossing, heb ik echt nog te weinig kennis van om te snappen wat daarmee gedaan wordt.
Hier moet ik nog eens tijd in steken.