Excel Formula help

mrbest · 10 Jun 2009 at 18:56

Hi guys,

I've created a tool in excel which reduces the number of characters in a title or line of text.

I want to add a formula so that the the tool can perform a more complex task: I want the tool to also remove any text within brackets plus the brackets.

Does anyone know if this is possible?

thanks in advance

mr best

miniyazz · 10 Jun 2009 at 19:21

I would use a macro. Others may have better ideas.

suarve · 10 Jun 2009 at 20:11

mrbest said:
Hi guys,

I've created a tool in excel which reduces the number of characters in a title or line of text.

I want to add a formula so that the the tool can perform a more complex task: I want the tool to also remove any text within brackets plus the brackets.

Does anyone know if this is possible?

thanks in advance

mr best

why ayve you created a tool when stuff like that is built into excel already? Seems like a waste of time.

Namsnik · 10 Jun 2009 at 21:08

Not sure what you mean by "a tool" if VBA or formula why not share it so we can suggest how to modify to achieve your goal?

Use Search to give character position of opening and closing brackets
Plug these results into Replace to remove the text
rerpeat in case their are further brackets in your text

mrbest · 10 Jun 2009 at 23:26

By 'tool' I mean a thing which makes a person's job easier/less time consuming, the literal meaning of the word.

I'm aware of the search function and I know how to 'find and replace' - This is what i have been doing, but I was hoping in addition to the formula I am using to reduce characters I could also have a formula to do this bit for me - So that I can simply paste text into a field and press enter, displaying the edited text. It's quite a simple concept.

Sorry if I sound like I'm having a go, I'm not, just trying to make myself clearer!

All I need to know is: Is there a formula which can remove any characters within brackets, as well as the brackets from a line of text which has been pasted into a cell.

Miniyazz - I'm not hot on macros, maybe this is something I should look into...

Cheers guys,

mrbest

Antar Bolaeisk · 11 Jun 2009 at 00:35

Is this what you mean?

I'm uploading this solution now and you can examine it to see how it works. I'll explain anything you can't work out.

Edit: Here it is.

Hmmm.... I didn't take into account that there are two spaces, one either side of the brackets, yet the final string seems to only have one space between "this" and "test". If it is a problem though it's easily rectifiable by simply adding 1 to the before brackets part.

mrbest · 11 Jun 2009 at 09:44

Antar Bolaeisk said:
Is this what you mean?

I'm uploading this solution now and you can examine it to see how it works. I'll explain anything you can't work out.

Edit: Here it is.

Hmmm.... I didn't take into account that there are two spaces, one either side of the brackets, yet the final string seems to only have one space between "this" and "test". If it is a problem though it's easily rectifiable by simply adding 1 to the before brackets part.

Mate! You are a true LEGEND! I knew it was possible. Thankyou so much for your help

mrbest

Antar Bolaeisk · 11 Jun 2009 at 18:52

Your very welcome, glad I could be of help.

miniyazz · 11 Jun 2009 at 20:45

Nice to know there are ways of doing things without using macros

Bit like electronics, if it doesn't work, just add in another relay

(private joke lol)

My problem with Excel is I never stopped to take the time to learn about all the little functions it can do, so I tend to go for the sledgehammer-to-hammer-a-nail solution to problems.

Antar Bolaeisk · 11 Jun 2009 at 21:54

Honestly it only took me the lenght of time between posting this over in the PC games forum:

Antar Bolaeisk said:
Good game tonight and apologies to all I inflicted myself upon. Obviously I've become rusty over the last week (though was I ever any different)

until about half twleve to arrive at that solution having never attempted to do something like the op was requesting before (whether you want to believe that or not is entrely up to you). It really doesn't take very long to learn the commands in excel, it's more about breaking the task down into parts then determining how to go about solving those parts.

The only major problem is knowing the terminology used in excel and computers in general. For example, if you don't know that the sentence used in the example above is referred to as a "String" then finding out what functions work with them can be a problem.

mrbest · 12 Jun 2009 at 00:21

Sometimes you can get so involved in trying to make something work that it takes someone else to show you something you missed that was right under your nose. That's what I love about these forums - everyone has a piece of knowledge that someone else is looking for

I've got the brackets thing working in my tool now - I'm just trying to adapt it so that it can remove multiple sets of brackets. I've realized that to do this I need to keep inputting the result into another set of the same formulas - the thing I can't work out is how to get the 'Results' field to display the final result (when there are no longer any brackets within the text) because if the result is put through the formulas again after the brackets have all been removed the result of the formulas is just "#VALUE". I think I need a formula to determine when all the brackets have been removed and then display this result. Any suggestions are welcome!

If you need me to upload the example let me know and I will do 2mo!

Thanks again,

mrbest

Antar Bolaeisk · 12 Jun 2009 at 10:39

Well I must admit that this was slightly more challenging than the original request but I believe I have it solved bar one or two bugs that may crop up during use. I agree with you though Mr Best that fresh eyes can often see a solution where someone who has been staring a problem in the face for a while can't see the forest for the trees as it were. That's why I beleive my solution this time may not be the best solution but it seems to work.

Basically it will convert this:

Into this:

Here is the file

This time around though I'm almost certain that there is a better solution than mine.

Edit: Those shots are from two different sheets in the document hence the cell ranges being the same.

Antar Bolaeisk · 15 Jun 2009 at 14:02

Hmmm... just thinking about this while mopping the floor at work last night, there is still a problem with the excel document I posted in that it expects there to be a space either side of the brackets but I've come up with a solution if ths poses a problem to you.

mrbest · 15 Jun 2009 at 22:54

Hey Antar Bolaeisk,

I've been meaning to get back to you about this but I've been well busy! Thanks again for your great input and for sharing your time to help me. What you posted before was great, the only problem was a lot of the functions aren't supported by the version of excel I'm using. However I was able to fix the multiple bracket problem in a less complex way. I'll post the excel file up here tomorrow so you can see how it worked out. Hmm you say you may have a solution to the space problem? That may still be useful...

cheers,

mrbest

leezer3 · 15 Jun 2009 at 23:31

If we're talking Excel formulas, any chance of your thoughts on this little problem-
I've got a sheet, with various values in it. I'm looking to find cells which look like this:

Code:

.curve NN;0

I then need to do one of three things to this cell-
1. If the value NN is zero, leave as is.
2. If the value of NN is above zero, add 200.
3. If the value of NN is below zero, minus 200.

Cheers

-Leezer-

mrbest · 16 Jun 2009 at 16:09

Hey Antar Bolaeisk,

Here's the tool I created. The formulas are hidden to the right of the input fields.

Leezer - I'm not sure with this. Anyone got any ideas?

Cheers,

mrbest

Antar Bolaeisk · 17 Jun 2009 at 21:54

Hello again Mr Best,

I've had a look at your file and while I like the approach you took I found myself getting lost (nothing to do with your file, just simply a lack on my behalf to get to grips with it) while trying to add in the extra bits needed for removing the spaces which is why I've returned to my earlier attempt (now with added backwards compatability

). Basically it checks both sides of the sentence split for spaces and ensures that there is a space on the left and that there isn't a space on the right unless there was never any space either side in the first place.

Here's the link

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

@ leezer3

Your request can be solved using nested if conditions as demonstrated in the file below (assuming I've understood you correctly):

Nested If

In both cases there is more than likely a better way but they both work I think.