Extract number between brackets in Excel?

excel extract brackets
Philip Ask Zeplin-Frederiksen picture Philip Ask Zeplin-Frederiksen · Mar 10, 2015 · Viewed 23.2k times · Source

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

Necho picture Necho · Mar 10, 2015

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.

Related questions

In Excel, how do I extract last four letters of a ten letter string?

For example, if I have ABS YUR YUAO HFH IWO OQNX YQO PQM QUCC How do I extract the last four letters in another column?

Read More
Excel - extracting data based on another list

I have an Excel worksheet with two columns (name/ID) and then another list that is a subset of the names only from the larger aforementioned list. I want to go through the subset list and then pull the data …

Read More
Replacing one character with another in a string

I have a data like below: A:B:C:D and I want to replace the C with data (say, Z) so that it may look like A:B:Z:D How can I do it?

Read More