Solved

Remove leading zeros

Posted on 2013-02-02
6
229 Views
Last Modified: 2013-02-02
I have two columns as text in a table (Access 2003)... I want to remove the trailing zeros from the list...  The data includes pure text as well..   Any help is appreciated...


Q6                            Q7
1-3773185931            389744857
1-3778572626            486467657
 R59129513                486251465
00201302519094970            213000102
00201302519080110            213000102
00201302220045640            00846882025
00201302919906304            213000100
00201302536387035            00846309580
00201302420029540            213000202
00201302519078350            213000202
00201302520045190            213000102
00201302919000370            213000202
00201302519096120            213000202
00201302520018000            213000202
00201302419011560            213000102
00201302519014000            213000202
00201302519014330            213000102
00201302519079950            213000202
00201302520018940            213000202
00201302519088320            213000202
00201302519079560            213000302
     00201302420031530            213000302
00201302220051050            00837108283
00201302220052690            00842741102
00201302519061900            00841219275
00201302885043580            00800201918
00201302520051720            00844234651
00201302219046940            213000102
00201302219046900            213000302
00201302219063080            213000102
00201302519080380            213000202
00201302519094420            213000102
00201302520030620            302927350
00201302819027160            302926050
00201302819002460            302924878
00201302519057090            302927345
00201302820007820            302925927
00201302820008450            302927313
00201302519080730            213000102
00201302519088500            213000102
00201302519077020            213000102
00201302819005250            302926254
00201301520068810            213000102
00201302519088600            213000202
00201302920004250            213000202
00201302519088980            213000202
00201302520056210            213000202
00201302419001280            213000102
00201302519079810            213000102
00201302519094690            213000102
00201302520025650            213000102
00201302519047720            213000102
00201302919017120            213000202
00201302520019880            213000102
00201302519077750            213000102
00201302520028090            302927146
00201302520028140            302927155
00201302520002290            213000102
00201302536389289            00849458833
00201302519006220            302924802
00201302319063770            00837101203
00201302520007100            213000202
00201302519057320            302926464
00201302519008650            302923894
00201302319042730            213000102
00201302519096410            213000102
00201302519095360            213000202
00201302319009420            213000102
00201302519007250            302924866
00201302520024620            213000102
00201302320039370            213000102
00201302520021100            213000102
00201302536385895            00840245142
00201302320039850            213000102
00201302419018790              302914475
00201302536382566            00842558466
00201302419004710            302915834
00201302519094930            213000202
00201302519080010            213000102
00201302319067700            00834169065
00201302220053780            00847340252
00201302519061290            00843607234
00201302220045670            00833505971
00201302520048690            00833486500
00201302219035070            00841236949
00201302220053690            00841207843
00201302220045810            00835940037
00201302536383400            00843224671
00201302919906308            213000100
00201302536383355            00845814533
00201302420030670            213000202
00201302536383646            00842982184
00201302519000140             302921025
00201302520031400             302920621
00201302936397044            00837586247
00201302520050670            00842523995
00201302436381425            00841042817
00201302519072250            00849449983
00201302536385076            00840424631
990264008            486678295
 890652328               486174119
FFS999999            389546005
1367776909            389443691
1-3779909711            390035366
1-3783260117            486652692
1377889655            389985656
1-3783017883            390144101
00201302536385186            00841758015
00201302519061840            00849689641
00201302420024780            00842951821
00201302536387592            00582833456
00201302536388185            00840502459
00201302520030580            302927334
00201302805009130            00836576200
00201302536385570            00835185983
00201302220045290            00842889182
00201302936397338            00579A66726
00201302936397032            00846293320
00201302220046330            00845529256
00201302819004280            302934248
00201302819009200            302935042
00201302820004510            302936145
00201302819025020            302935787
00201302820004420            302936086
00201302919014540            213000502
00201302919012070            213000202
00201302919007650            213000302
00201302520023650            213000302
00201302820004660            302936202
00201302519092630            213000102
00201302819007630            302934537
00201302820000300            302930677
00201302819009520            302935196
00201302519080470             213000202
00201302519078600             213000302
00201302919017060             213000102
00201302820033460             213000102
388573348            No Change, All Findings and Assignments Were Retained
990239966            485905557
990209619            485532702
1-3756796421            388980857
389780784            839780784
891264122            485365047
201301520021690            Change the Finding
201235620021760            No Change, All Findings and Assignments Were Retained
201302219909135            Change the Finding
800593176            486686546
1-3772834564            389741717
13767590448            389447311
1-3773622856            389766381
893612597            486669645
1-3773893444            387658478
1-3781303525            390080844
1-3780372142            390038368
894315394            486619534
1-3780236479            390040501
99B562766             486252288
13766719632            389403075
1-3780749143            390051873
99B643236               486267152
13770592721            389700339
 990262182              486240043
1-3783488267            390171657
 990235751              486521528
893813132            486548872
1-3783107488            390157807
912322166            486555194
1-3776927673            389909268
 R22861360              486212729
1-3780744461            390055492
1-3775605843            389850888
1-3783010439            390149647
916525972            486655720
13765321152            389324545
99B554576              485882122
1-3782522508            486662017
1-3782538693            486669824
 99B304719             485957945
1-3781666869            486603421
1-3774665373            389813367
FFS999999            485949628
FFS999999/922035923            486589442
894479325            486671934
13770015593            389527408
922447640              486239009
920451331            486590490
1-3780760361            486564304
FFS999999            486571155
1-3780749023            380050718
1-3782798179            390139648
390127040            390127040
1-3781805244            390105024
 890615925               486254435
916332524            486642732
990091874               486632519
920439202            486601512
1-3782910009            486668782
919880374            486654544
 1-3782192566            390117868
13765281567            389325967
1-3783267150            486690177
1-3776854186            389896660
1-3777321744            389923356
 990226066               486505047
1-3782566072            390124682
13770929821            389701501
1-3782689579            390138982
1-3781019421            486580128
1-3775304639            389563576
13776000505            389880275
1-3776879133            389895551
1-3776879133            389955407
1-3780382543            486553434
1-3781368748            390089053
13779393645            390011820
  990169574                 486224189
1-3777728299            389939375
892331797            486540886
1-3780851507            486571885
1-3782674629            486668921
1-3778661148            389976001
893182940            486570081
1-3782970064            486672318
1-3777913105            389947387
ffs999999/486563597            486563597
  990221239                 486208032
893076875            486543373
1-3781129139            390071824
1-3776941645            389906532
1-3779604146            390020468
1-3777534023            389923406
893045499            486558888
ffs999999/486632054            486632054
920431203            08020228
920455032            486692762
1-3780382655            486560915
1-3781468729            486620563
 897054068             486205454
891106848            486631266
910969749            486584671
13783102415            390156405
1-3782301890            486643505
1-3782908484            390147186
1-3776813101            389894547
910311023            486603724
1-3778430632            389973142
1-3781239377            390075792
891700715            486621777
1-3783419490            390172315
1-3782691018            390130666
1-3782757094            390135585
891097357            486626586
1-3783095676            390163385
1-3780838263            390057601
1-3779193602            390006175
1-3781552782            486610049
1-3781585094            1-3781585094
1-3783015825            486669436
obt999999/ 08038817            486595401
1-3783086658            390160995
1-3778957227            389991494
1-3781496341            486602244
924231973            486700184
1-3781674145            390094108
99B575814              486564881
13766480872            389370370
1-3779505274            390016945
obt999999/ 08038386             486585292
 99B557132              486539967
1-3780685899            390048098
  990271070                486539396
13769348172            38949100
1-3781496470            486605391
13774265208            389796423
1-3777220392            389914016
1-3777804725            389947160
1-3781927953            486614196
1-3782643797            486679345
1-3783565213            390171915
1-3783169422            390163555
1-3780236581            390040951
1-3781541365            486606651
 893547699              486643944
1-3777097853            389902145
1-3780810297            486574034
1-3781306211            390075699
 890656657              486645539
1-3780131489            486549181
1-3778038856            389945148
1-3779493549            390003006
 990021068               486666992
1-3778507954            389968044
1-3782966913            390141240
1-3782066063            486623875
1-3778039447            389954824
1-3782066063            486538583
1-3782386954            486639708
891354084            8020466
1-3776730752            389897671
990143398            8029505
1-3778905355            389991644
ffs9999999/ 08041443             486658613
1-3779355904            390008514
1-3778261420            389965900
1-3777012776            389916772
1-3776127924            389878547
 99B728156              486683217
990050999            486611663
1-3774684055            389812494
1-3776873418            389900606
1-3777408606            389926593
485924438            485294438
890340424            486592025
990249744             486620383
1-3779368750            390020115
99B590935            486557219
1-3780994831            390058114
990081803            486705959
1-3782133834            390104370
1-3778382530            389973390
1-3781110841            390063653
893384644            486601679
1-3780473574            486553556
1-3781790282            390098194
1-3781464535            486600332
892635485            486219254
1-3779413150            390013012
920455412            486261852
485892332            485892332
1-3783284358            486698294
1-3777920604            389959633
918859810            482611806
r58820201            486559334
990061777            485905146
13778972095            389995244
990150375            485940870
890790718            8028930
Met All Expectations            Exceeded Expectations
Exceeded Expectations            Exceeded Expectations
Exceeded Expectations            Exceeded Expectations
1-3785383922            390229135
1-3778661371            389978358
1-3784394532            487038620
990081771            486602163
910324807            486241604
38919173            389819173
1-3782082559            390124114
99a940069            487144006
893526497            486236363
1-3786520028            390286551
990149296            486439753
390147879            390147879
1-3785875346            390258518
990182811            486658206
99b585368            486714384
13775303667            389850310
1-3786812973            390296109
1-3784401017            390194106
13779506494            390023236
890611011            486552639
13778829723            389991973
1-3784113975            390180190
1-3785267051            487074710
1-3784796747            390216313
1-3784373785            390198544
1-3784373503            390193668
1-3786465834            390271930
1-3784835455            390214944
1-3773758321            389772191
1-3785078188            390231477
1-37862326665            390284656
1-3784319773            390196048
892028030            487097864
914330212            48660295
390294060            390294060
13774691450            389838533
892031177            8021304
1-3785647701            390252842
1-3786303073            390275893
13778835556            389992195
891850770            486642063
13783158893            390157275
1-3781223755            390086901
FFS999999-920145184            487140255
1-3781223344            390076200
915654298            486588072
1-3781223755            390086901
1-3786136433            390270215
892652857            8023403
13775188553            389842973
1-3784682865            390199369
922438842            486630548
1-3783621741            390174871
FFS9999999            487162515
1-3782638879            390127103
1-3778600796            1-3778600796
1-3779050887            1-3779050887
1-3781150715            390079969
390103703            390103703
1-3786136433R            390270215
1-3780779864            390050614
1-3786136433            390270215
390256049            390256049
1-3784635958            390197265
892642395            486730184
390251120            390251120
390252892            390252892
1-3785316378            390237639
1-3741216493            388258000
13781971891            390097741
893356634            486676893
13779274202            390004544
922438842            487156438
892760719            8027444
Met All Expectations            Met All Expectations
Exceeded Expectations            Exceeded Expectations
Exceeded Expectations            Exceeded Expectations
Met All Expectations            Met All Expectations
Exceeded Expectations            Exceeded Expectations
Exceeded Expectations            Exceeded Expectations
Exceeded Expectations            Exceeded Expectations
Exceeded Expectations            Exceeded Expectations
Met All Expectations            Met All Expectations
Exceeded Expectations            Exceeded Expectations
Exceeded Expectations            Exceeded Expectations
0
Comment
Question by:eiyengars
  • 3
  • 2
6 Comments
 
LVL 16

Accepted Solution

by:
kmslogic earned 500 total points
ID: 38846956
Your question title says remove leading zeroes and your question text says trailing so this function will do both:

Put this function in a  module

Public Function TrimZeros(ByVal pvar As Variant) As Variant
    Dim i As Integer
    
    If Not IsNull(pvar) Then
        pvar = LTrim(RTrim(pvar))
        
        ' Trailing
        
        For i = 1 To Len(pvar)
            If Right(pvar, 1) = "0" Then
                pvar = Left(pvar, Len(pvar) - 1)
            Else
                Exit For
            End If
        Next
        
        ' Leading
        
        For i = 1 To Len(pvar)
            If Left(pvar, 1) = "0" Then
                pvar = Right(pvar, Len(pvar) - 1)
            Else
                Exit For
            End If
        Next i
    End If
    
    TrimZeros = pvar
End Function

Open in new window


Then in a query

UPDATE YourTable SET Q6=TrimZeros([Q6])

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38847000
You can use this expression:

NewValue: IIf(IsNumeric([Q7]), CStr(Val([Q7])), [Q7])

/gustav
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38847153
What if it is that value is 00NoWayMan0?  But if the value is always numeric and you just need to strip leading and not trailing 0's that's a much cleaner way.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38847463
Right, that's what I thought given the example data.

/gustav
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38847867
Can you list just few representative records and the expected output?
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38847889
That's kind of like our line to you.  It is helpful for you to give us the expected input and output.  You could also just try our solutions and see for yourself.

In any case, Cactus's code will do this:

000102550 turns into 102550
000BOB100 turns into 000BOB100 (because it isn't numeric it returns original value)

my function as is will strip both leading and trailing zeros (although you could easily remove the portion for either if you wanted one or the other and will work even if the string contains non numeric values.

000102550 turns into 10255
0000000Hello World turns into Hello World
00FADDC00 turns into FADDC
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now