Converting text to number

[cmb80]

Hi there, I have a formula which extracts a number from a text string.

the text string is in A2 - the formula which extracts the number is in B2

However - when I copy the data in B2 and paste values to another sheet / cell - the forumla is as text - and I get the little green corner symbol and I have to convert to number.

Please advise some VBA code - that will automatically convert my extracted "number" - to an actual number in number format.

Thanks in advance

[icestationzbra]

generally, you can use the formula that you are using to extract numbers to also convert them to the proper format. if you can share the formula, we could update that.

another option is to use the Paste Special method. put the number 1 in a blank cell - copy it and then Paste Special > Multiply over the range that needs to be converted to numerical format.

[Fotis1991]

...the text string is in A2 - the formula which extracts the number is in B2

Without to know which is this formula, just an idea..

Add +0 in the end of your formula in B2.

[dbrault]

I use asap-utilities for this, excel add on. asap-utilities.com

[cmb80]

generally, you can use the formula that you are using to extract numbers to also convert them to the proper format. if you can share the formula, we could update that.

another option is to use the Paste Special method. put the number 1 in a blank cell - copy it and then Paste Special > Multiply over the range that needs to be converted to numerical format.

Well i actually have two bits of code.

One is

Please Login or Register  to view this content.

One is

Please Login or Register  to view this content.

Both of these extract a number (where applicable) - but the number always saves as text

[cmb80]

Without to know which is this formula, just an idea..

Add +0 in the end of your formula in B2.

This actually seems to work - thank you

[Fotis1991]

You are welcome. Thanks for the reb*

[icestationzbra]

the INDIRECT function being used in those formulae is considered to be VOLATILE, which sort of means that the spreadsheet runs a lot of unnecessary calculations, making it processor intensive.

as far as i could understand, in your specific case, INDIRECT("p"&ROW()) translates to INDEX(P:P,ROW()), which is non-VOLATILE and really really fast.

if you share some sample data and expected results, i have a hunch that we can simplify and enhance the formulae.


UPDATE:

this formula may not seem shorter / simpler, but it will be faster / efficient (and give you numerical output, too) over large ranges of data:

Please Login or Register  to view this content.

when compared with,

Please Login or Register  to view this content.

[cmb80]

Sorry, only just seen this response. Thanks for the tips, my formula's can no doubt be improved for speed. I have a lot of calculations involved - and probably not working at the most efficient way.