INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Formula to remove bracketed text

Formula to remove bracketed text

(OP)
Hello all,

I am using the following formula to detect and remove brackets and the text within it however I need it to loop as some fields have more than one instance of bracketed text. The formula is:

IF INSTR({EstDescription.Description}, "(") = 0 THEN {EstDescription.Description}
ELSE
LEFT
({EstDescription.Description}, INSTR({EstDescription.Description}, "(") - 1)
+
IF INSTR({EstDescription.Description}, ")") = 0 OR INSTR({EstDescription.Description}, ")") = LEN({EstDescription.Description}) THEN
""
ELSE
RIGHT
({EstDescription.Description}, LEN({EstDescription.Description}) - INSTR({EstDescription.Description}, ")") - 0)


Can anyone advise the best way to loop the above formula?

Thanks in advance
J

RE: Formula to remove bracketed text

If you just want to remove the characters, I would probably use 'Replace' in a pair of formulas.

{@ReplaceLeft}
Replace({EstDescription.Description},"(","");

{@ReplaceRight}
Replace({@ReplaceLeft},")","");

disregard the above....i reread the question and see that you want to remove the text within the () also.
let me look through my notes I am sure i have done what you are asking.

RE: Formula to remove bracketed text

(OP)
Thanks for the suggestion, but i need to remove the text within the bracket also.

RE: Formula to remove bracketed text

(OP)
@fishermacse... Your suggestion got me thinking and I have actually got it working in a Pair of formulas, or formula in a formula.

One thing i notice it's doing, is leaving a space where the bracketed text was... any ideas how to close that space
e.g. "250gsm ,"
The bracketed text was after gsm but after removal the comma is floating.

RE: Formula to remove bracketed text

This may not be elegant but it works. Replace the first line with local stringvar RAW := {table.yourfield}
It will not handle double close brackets well.

local stringvar RAW := "ABC[DEF]GHI[HIDE ME]JKL";
local numbervar I :=1;
LOCAL NUMBERVAR GOSTOP := 0 ;
local stringvar cooked := "";
while I <= len(RAW)
do (if Mid(RAW,I,1) = '['
THEN GOSTOP := 1
ELSE
IF GOSTOP = 2 THEN GOSTOP := 0
ELSE
IF MID(RAW,I,1) = ']'
THEN (GOSTOP := 2 );
// ELSE GOSTOP := GOSTOP;
IF GOSTOP = 0
then cooked := cooked+mid(RAW,I,1);
I:= I+1) ;
local stringvar cooked

RE: Formula to remove bracketed text

2
I'm not suggesting this is a better approach, but it looked like an interesting challenge so thought I would have a go at a solution.

This is what I came up with:

CODE

WhilePrintingRecords;
Local NumberVar i ; 
Local NumberVar C := LEN({Table.Field}) - LEN(REPLACE({Table.Field}, '[', ''));
Local StringVar S := {Table.Field};

For i := 1 to c do
    S := LEFT(S,INSTR(S, '[') - 1) + MID(S, INSTR(S, ']', INSTR(S, '[')) + 1);

S 

This works for text between square brackets , ie '[' and ']'. If you are looking for text between '(' and ')' the formula will need to be changed.

It assumes that every opening bracket will have a corresponding closing bracket.

Hope it helps.

Cheers
Pete

RE: Formula to remove bracketed text

(OP)
Thanks for all your suggestions... much appreciated!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close