Left field / Right field

X

Xazn

That works on most but not on the items with letters in
the middle. 1000H03 turned up as #ERROR and the column
is text type. It does work on the data that is only
numbers. This is whats entered.

Item_no : 'CSL' & Left([Item #],Len([Item #]-2)) & "-" &
Right([Item #],2)
-----Original Message-----
I have a table with text (numbers and letters) that vary
from 6 to 20 characters long. I want to add a '-'3
places from the end on every character in the one
column. An example is CSL2414105 would be changed to
CSL2414-105. Not sure if it is easier in SQL, Access, or
Excel. Any help would be appreciated.
Click to expand...

An Update query in Access would do this pretty readily:
base an Update
query on the table, and update the field (I'll call it
MyField, use
your own fieldname of course) to

Left([MyField], Len([MyField] - 3)) & "-" & Right
([MyField], 3)

John W. Vinson[MVP]
 
Top