Extract number between brackets in Excel?

Question

I've been searching up and down for an answer to this, but haven't found anything I can get to work. It may well be my lack of knowledge in the more "codey" aspects of Excel.

Anyway, this is my situation: In C2-C17, I have some text set up like this:[2] Name of job. The "[2]" indicates the hours we expect the assignment to take, the rest is the name of the assignment.

Now I need to calculate how many hours all this adds up to, so in B2-B17 (the numbers will change) I would like to extract the number between the brackets, in this case "2", so I can simply calculate and add them up.

How would I go about doing this? I've spent far too long at work by now, trying to find a solution that works for me.

Any help would be very much appreciated!

Answer 1

Use the formulas: MID(cell,start_num, length) and FIND(text,cell)

So, the formula is:

=MID(B3, FIND("[",B3)+1, FIND("]",B3) - FIND("[",B3) - 1)

Which means: Extract the text from the cell B3, starting from the location of character "[" plus 1 in the same cell, and a total of chars calculates by: location of "]" - location of "[" minus one.

Answer 2

There is a function to work with substrings: "MID"

The C3 cell have formula. We assume that string will always start from [ and then will be number of hours (1 digit).

Than I just expand to other cells froc C3 to F3

You can add substitute function to work with two digit numbers.

Answer 3

Copy to a new column, Find & Replace, Find what:

*[  

Replace All. Find what:

]*  

Replace All.