Formula

building reports webintelligence
1

I have a report which outputs 1st Line of the address into one column require formula which separates the house number or name from the string.

Jacks1 (BOB member since 2009-11-09)

2

Welcome to the forum! :slight_smile:

Do you have a delimited between the house number and name like a comma? You could find out the position of the comma and use functions like Right, Left, Pos, Length on the string to derive the House number and name.

I tried out a sample on the E-fashion Universe. Check the screenshot for details. What I tried to do was split up the Store name into “e-Fashion” and the Location using SPACE(" ") as the delimiter.

Formula for FIRST PART was;

=Left([Store name];Pos([Store name];" "))

Formula for SECOND PART was;

=Right([Store name];Length([Store name])-Pos([Store name];" "))

sample report.doc (150.0 KB)

nithya_raj (BOB member since 2007-02-03)

3

the record is not comma delimited

Jacks1 (BOB member since 2009-11-09)

4

So how do you know what to separate?
Is there some sort of business rule?

5

Welcome to B:bob:B! Moderator Note: Please let us know which tool you use. Desk-I? Or Web-I? We’ll move it to the right place. Thanks.

P.S: The suggested formula has nothing to do with a comma, it is for a space limited string.

Jansi :india: (BOB member since 2008-05-12)

6

Hi, please let us know which report tool you are using, thanks.

Dave Rathbun :us: (BOB member since 2002-06-06)

7

Moved to Webi forum for now. If the original poster comes back and can confirm that it’s a different tool, we will move it again. Thanks.

Dave Rathbun :us: (BOB member since 2002-06-06)