How to clean text/convert to numbers in Excel?

I am working with a spreadsheet that has a single column filled with text that is made up of numbers, sometimes followed by random gibberish, i.e.:

227[1]
107.8682(2)[2]
26.9815386(8)
243[1]
39.948(1)[2] [3]
74.92160(2)

I would like to come up with a way to extract the numerical value itself (i.e. somehow get rid of the stuff afterwards that is in () and [] brackets) and convert it to a two-decimal number in another column.

Any help is greatly appreciated!

You can do this by setting up three new columns: one for the location of the first "(", if there is one, one for the first "[", if there is one, and one to use these locations to extract the numerical value.

Let's say the text is in column A. Then you would have these formulas in B, C, and D (row 2):

B: =IF(ISERROR(SEARCH("(",$A2)), 256, SEARCH("(",$A2))

C: =IF(ISERROR(SEARCH("[",$A2)), 256, SEARCH("[",$A2))

D: =VALUE(LEFT(A2,MIN(B2:C2)-1))

You can format column D to be a number with 2 decimal places (Format - Cells - Number tab, etc.).

If you ultimately want to get rid of the column with the text, you will want to select the column with the extracted numbers (D), and Edit-Copy, and then Edit-Paste Special-Values.

I hope this helps, but add more details or e-mail if you need more info.
Good luck.
.

Hi there.

You really have a challenging question. I won't be able to tell you the exact, fastest way of extracting the numbers, here's a few suggestions I came across.

Check this site first tho and see if u can understand the coding suggested there.:

http://en.allexperts.com/q/Excel-1059/Ex...

Private message me back and as to find a way of me to send you an excel file i made up of my dirty way of extracting your numbers.

Hope this helps.