edit table data

[Xazn]

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.

 

[John Vinson]

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.

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]

 

[xazn]

That worked 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)

 

[John Vinson]

That worked 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)

Could you post the complete SQL of this query? Are you running an
Append query, an Update query, or what?

John W. Vinson[MVP]