Convert text with numbers to values while keepingthe text

Hello,

I have lots of data, all in the text format. I need to convert the numbers stored in the text format to values. Format Cell=> Numbers doesnt work.

When using VALUE function it perfectly converts the numbers stored in the text format to their actual values, but when this fuction deals with text cells, it ends with #VALUE.

Example

0123 123
ABC #VALUE


Is there universal formula that can convert the text numbers into values and what it cant convert - leave as it is.

Example

0123 123
ABC ABC

Many thanks,
Anton

Try it like this;

Formula:

Please Login or Register  to view this content.

Ok great it works many thanks!!