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

x
?
Solved

Access formula to Count proceeding spaces in a field

Posted on 2013-11-14
2
Medium Priority
?
442 Views
Last Modified: 2013-11-14
I have a table that contains text values with proceeding spaces. I want to add another column in my query that provides the count of the proceeding spaces. There is also spaces of the back side of the text that I do not want to count.

In excel I use the flowing formula, but I can not figure out how to translate in access.

 =FIND(LEFT(TRIM(A1)),A1)-1

Thanks!
0
Comment
Question by:Alphacircuits
2 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 800 total points
ID: 39647899
Try this:

for left spaces:

= Len("" & A1) - Len(Ltrim("" & A1))

For right spaces:

= Len("" & A1) - Len(Rtrim("" & A1))



For All spaces:

= Len("" & A1) - Len(trim("" & A1))
0
 

Author Closing Comment

by:Alphacircuits
ID: 39647920
Thank you very much!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question