0

Good afternoon,

I was wondering if there is a way to count the number of blank spaces in a cell and return that value using a combination of vlookups, if else statements, and possible the len function? For example, the reports that are generated Generate values like this:

"TopLocation" " SubLocation" " SubSubLocation"

My goal is to be able to automate some of these reports, but I feel like I'm missing one or more pieces to the puzzle.

Thank you.

John
  • 1
  • 1
  • 1
    I added an answer for what I think you're trying to do. Your question would be much improved if you added the expected output along with the input (assuming those three in the middle are inputs). – Byron Wall Jun 16 '15 at 20:21

1 Answers1

1

Assuming a blank only refers to a simple space, , you can get this with a quick formula that takes the difference of the original length and the length after removing spaces.

Formula in B1 with data in A1, copied down to end of data

=LEN(A1)-LEN(SUBSTITUTE(A1," ", ""))

Picture

picture of results

Data in case you don't believe the image, where space is now period

a.b
a...c
..a..c
Byron Wall
  • 3,970
  • 2
  • 13
  • 29