What is the function to count number of spaces in a cell.

[Former Member]

Hi Experts,

In Webi, i am stuck with one scenario. The scenario is I have a column named Customer Address. In that i want to know how many "spaces" are there for the each row.

I have created variable and tried using various functions like Count, Substr etc... but those are not appropriate for this scenario. Please Help me.

Customer Address
1400 RamNivas Opp.Croma Circle6 Gurgon
11-88 KPHB Opp.BrandFactory Hyd
1-4-8 ChitrangColony Vijayanthi Chennai

I want output like Below,

Address has to display till second space or till third space or till fourth space........

based on the client decision i will select the address length.

[Former Member · Accepted solution]

Hi Sreeram,

I hope you are talking about Webi here. This is what I would do if I really want to achieve this. I am not saying this is the best way to do. Give it a try and decide if you can achieve the same results by using some other functions. Here is my answer:

Lets assume that our address is "1st 2nd 3rd 4th ".

If your customer decides one space then you will display "1st". if 3 then "1st 2nd 3rd" should be displayed.

The following example formula should display the values before the 2nd space.

=Left([CAddr];(Pos([CAddr];" ")+Pos(Right([CAddr];Length([CAddr])-Pos([CAddr];" "));" "))-1)

How it works on "1st 2nd 3rd 4th ":

1. Get the position of 1st space. You will get 4.

Pos([CAddr];" ")

2. Get the remaining string after the 1st space. You will get "2nd 3rd 4th ".

Right([CAddr];Length([CAddr])-Pos([CAddr];" "))

3. Get the position of 2nd space in remaining part of the string. You will get 4.

Pos(Right([CAddr];Length([CAddr])-Pos([CAddr];" "));" ")

4. Get the position of 2nd space in the whole address. You will get 8

(Pos([CAddr];" ")+Pos(Right([CAddr];Length([CAddr])-Pos([CAddr];" "));" "))

5. Get the string from left until the position of 2nd space. As we will get a leading space we need to subtract 1 from the above formula. You will get "1st 2nd" as result.

=Left([CAddr];(Pos([CAddr];" ")+Pos(Right([CAddr];Length([CAddr])-Pos([CAddr];" "));" "))-1)

All you need to do is replace "CAddr" with your column name. If your customer decides 3rd position, you can edit this formula to add another step.

This formula should give you some ideas to get started with. Let me know if something is not clear.

Sujit.

[Former Member · Accepted solution]

Thank You Mr. Sujit for your reply.......

I have done this in a different way...using "Substr" and "Pos" functions... i feel its too lengthy....

I just want to know is there any direct function to solve this problem ?

[Former Member · Accepted solution]

Hi Sreeram,

I can't think of any direct function. I can understand the pain its going to cause, when you decide to go for a position that is higher than 4th or 5th SPACE.

Sujit.