Changing the length of a number

CesarAustin

Board Regular
Joined
Aug 21, 2004
Messages
145
Hope someone can help me, I have a table with one column with numbers from 1~999 I need to do some kind of update quarry or something to change the 1 and 2 digits to 3 digits… example: number 1,2,3 changed to 001, 002, 003 etc.. I have no idea how to do that without exporting the information from access to excel then manually changing the numbers in excel. Any help would be great.. Thanks all
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

pritcham

Board Regular
Joined
Oct 26, 2005
Messages
128
Hi

Try something along the lines of (SQL query):

Update myTable
Set myField = left("000",3-len(myField)) & MyField

Assuming you want the resulting field length to be 3 chars - if you want 4 then change the "000" to "0000" and the 3 to 4 and you should be set.

HTH
Martin
 

CesarAustin

Board Regular
Joined
Aug 21, 2004
Messages
145
Thank you pritcham… I’m not that good in make SQLs to work… my table is called “Systems Location”…. In that table I have two columns… the 1st column (name: “FLOOR”) has letters… and in the 2nd columns (name: “Jack Num”) I have the numbers.. 1~999… I want to change the 2nd column (or add a 3rd, what ever is easier) and add the 1st column to the 2nd column.. Example.. A001, B002, C015, D100.. etc…
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Why not just format the number field as 000?

If you want to get A001, B002... etc you could use this expression in a query.

[FLOOR] & Format([Jack Num], "000")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,675
Members
412,481
Latest member
nhantam
Top