There are times when you bring data from other software to excel sheets and find that some of the numbers are formatted as text. It is necessary to convert them to numbers in order to use them further.

Our post will guide you how to convert such strings to numbers – we will take up number of ways to convert them to their proper format. So let’s start by taking up examples:

Example # 1: Number Containing Commas and Spaces:

Let’s consider a number that has commas as separator.

Though this number should behave as a number, if it does not, we simply need to substitute the commas – using a formula by making substitution:

=ABS(SUBSTITUTE(A1,",",""))

The above number had extra spaces in the beginning and commas as separator. The SUBSTITUTE() function takes following argument to get rid of the commas:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

The text is the text in cell A1, Old text is comma that has to be substituted, the new text is a blank space, instances is optional and when we leave it – all the occurrences will be replaced. For the spaces that are in the start, we have used ABS() function to get rid of them.

The result is a cleaned up number:

Using CLEAN() to get rid of unprintable character:

Most of the time the function is use to clean up the text with TRIM() but our case we are using it to get rid of unprintable characters in any number. There are quite some times when numbers as text have unprintable characters and extra spaces. For such cases, we can use TRIM() and CLEAN().

The CLEAN() function just take one argument and that is the number. And the ABS() function return the absolute value out of it.

The final formula is:

=ABS(CLEAN(A3))

That is all for this post – will come with some more interesting stuff in our next post.