ICAEW.com works better with JavaScript enabled.

When Text into Numbers won't go

I often get sent spreadsheets containing issues to try and sort out. Sometimes, it's the seemingly straightforward problems that can prove to be the most difficult to solve.

VALUE()

Recently someone sent me a small spreadsheet that included a column of formatted numbers. Unfortunately, the numbers weren't numeric values that had been formatted as numbers but had been entered (or more probably pasted from elsewhere) as formatted text, complete with commas and pound signs. Normally, this wouldn't be a problem. The Excel VALUE() function would recognise the pound signs and commas, discard them, and return a numeric value.

The client had already tried this approach and VALUE() had returned a #VALUE! Error. There are several text functions that can be useful in these circumstances.

Text functions

TRIM() removes leading, trailing and multiple spaces. In this case you might expect it to remove the multiple spaces between the pound sign and the first actual number. But it doesn't.

Find out more

Members of the IT Faculty, Excel Community and subscribers to Faculties Online

Full article only available to IT Faculty members, Excel Community members and subscribers to Faculties Online.

Non-members

To read the complete article join the IT Faculty, Excel Community or subscribe to Faculties Online.