Solved

Access formula to Count proceeding spaces in a field

Posted on 2013-11-14
2
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 200 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

726 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