[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Cut off first few characters in field in table

I have a text field in my table:

123456789 / Last Name First Name

Before importing from Excel, I simply have Excel delete the first 12 characters, then I import it into the database. How do I tell the table in Access to cut off these characters when they're imported so I don't have to do it in Excel first?

Thanks!
Kevin
0
Kevin Smith
Asked:
Kevin Smith
  • 4
  • 2
1 Solution
 
rockiroadsCommented:
mid$([namefield],13)

in your query, get your name to be selected like this, this then removes first 12 characters

select field1, field2, mid$([namefield],13) as cutname
from table
0
 
rockiroadsCommented:
actually what might be better (since I re-read your question)

is to use import export specifications and set it that way

or just run an update command afterwards

update table
set namefld = mid$(namefld,13)



0
 
Kevin SmithAuthor Commented:
How would I do the import/export specs?
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Kevin SmithAuthor Commented:
The import export specs works only for importing another db, correct?  I'll be importing from excel.
0
 
Kevin SmithAuthor Commented:
Okay, I got it, but it seems to be cutting it from the wrong direction (counting back from last letter)...how do I start the cut at the first letter (left)?
0
 
Kevin SmithAuthor Commented:
Nevermind...I got it :)

Expr1: Mid([Payee ID/Name],13)

Thanks!
Kevin
0
 
GRayLCommented:
Are you trying to clean up the field using the import spec?  I think you would be better off importing all the data and then running an update query like rockiroads suggested. Confirm you are using a European or NA installation and not a read right to left?
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now