#NA error while using the formula in extracting numbers from alphanumeric excel 2007

[rajuj]

Hi,

Please find enclosed herewith a excel sheet, where i am getting #na error for a formula used. It shows as a value is not available for formula to work. It also does not show, show calculation steps.
But, if i click the place before the fx button, it shows the extraction correctly.
When i clicked the trace the error, it says that the arrows denoting some circular references and i need to close all the arrows.
Of course, i copied the formula, as i am not well versed with mid function. Would gurus come to the rescue as always.

[:) Sixthsense :)]

It is an Array Formula so instead of normal enter Press F2 and use CTRL+SHIFT+ENTER

[rajuj]

Hi sir,
When i tried by selecting one row of my data i get value error. a value used in this of wrong type error. Please correct it and send as enclosure,

[:) Sixthsense :)]

On which row with which value?

Sorry at present I can't attach files

[rajuj]

#value error at the first row itself. I copied the formula used f2 and then crol shift enter keys.I attached my query with the problem file

[:) Sixthsense :)]

Place the cursor in B1 cell and Press F2 and use CTRL+SHIFT+ENTER.

Drag the B1 cell down...

[rajuj]

Thanks . It worked . But it worked for numberical without any decimal. Can you suggest how to add to the condition to add the decimal. for eg, if the alpha numeric is hju12l4m5.0546. Could this be extracted as 1245.05466

[:) Sixthsense :)]

With that formula it cannot be done, since dot is a text character.