Formating part numbers after import from Excel

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I am importing part numbers from an Excel Spreadsheet into Access 2007 . the
field name is PART_NUM

The partunmber field is text and is 12 digits in length

I want to do an update query that will place dashes into the part number
after every three letters or numbers for example 000-000-000-000 or
123-abc-768-def

I have tried several combinations but none of them work properly

Left([PART_NUM],3) & "-" & Mid([PART_NUM],4,3) & "-" & Mid([PART_NUM],6,3) &
Right([PART_NUM],3)



I even tried using the len field to no avail


Left([PART_NUM],Len([PART_NUM])-6) & "-" & Mid([PART_NUM],4,3) & "-" &
Right([PART_NUM],Len([PART_NUM])-6)

Any suggestions would be much appreciated

Tom
 
I am importing part numbers from an Excel Spreadsheet into Access 2007 .
the field name is PART_NUM

The partunmber field is text and is 12 digits in length

I want to do an update query that will place dashes into the part number
after every three letters or numbers for example 000-000-000-000 or
123-abc-768-def

I have tried several combinations but none of them work properly

Left([PART_NUM],3) & "-" & Mid([PART_NUM],4,3) & "-" &
Mid([PART_NUM],6,3) & Right([PART_NUM],3)
Click to expand...

Try using Format(FieldName, "@@@-@@@-@@@-@@@")
 
You're a genius that worked perfectly with the formatting suggestion. One
more quick question though: Not all of the part numbers are perfectly uniform
some only have 11 characters . is there a way to automatically add a zero
whereever a digit is missing just to make the character number identical
during the formatting change?

Thanks so much !!

Tom

Rick Brandt said:
I am importing part numbers from an Excel Spreadsheet into Access 2007 .
the field name is PART_NUM

The partunmber field is text and is 12 digits in length

I want to do an update query that will place dashes into the part number
after every three letters or numbers for example 000-000-000-000 or
123-abc-768-def

I have tried several combinations but none of them work properly

Left([PART_NUM],3) & "-" & Mid([PART_NUM],4,3) & "-" &
Mid([PART_NUM],6,3) & Right([PART_NUM],3)
Click to expand...

Try using Format(FieldName, "@@@-@@@-@@@-@@@")
Click to expand...
 
Where do you want the zero: at the end or the beginning?

For at the beginning, use

Format(Right("000000000000" & FieldName, 12), "@@@-@@@-@@@-@@@")

For at the end, use

Format(Left(FieldName & "000000000000", 12), "@@@-@@@-@@@-@@@")
 
Back
Top