MS Excel tip for converting cell format from text to number

In the process of manipulating alphanumeric data in MS Excel, you may find the need to change cell formatting from text to number.  Sure, our programmer friends might nest their data manipulation function within a VALUE function.  The rest of us, though, might not remember that function–and might not want to spend the time finding it AND figuring out its syntax.  Instead, we might fall back to our old standby–highlight the selection, right-click, select “Format Cells” and select the category.  Here’s something else you might try that is somewhere in between.

Say you have the following data.  (This happens to be from a time-phased view of Microsoft Project, but yours could be anything.  Visualize many hundreds of columns, and then you’ll appreciate the potential time savings.)

1a

You want to strip the alpha portion of the data shown, leaving the numeric portion for further processing.  You know to employ the following formula (relative to your range)

=LEFT(A1, LEN(A1) – 1)

>to strip the trailing character, thus leaving you with a number, right?  Well, sort of.  You’re left with a digit(s) formatted as text to which you can apply no mathematical operations.

2a

Here’s where you could explicitly force the format conversion with the VALUE function or perhaps apply the “Format Cells” functionality as discussed above.  OR…how about this?  You could simply added two keystrokes to your LEN function and take care of the text-to-number format change with one easy change.  Here’s how.

=LEFT(A1, LEN(A1) – 1)*1

Voila!  You now have data that have been converted from text to number format without nesting the LEN function within a VALUE function–and without using our familiar “Format Cells” functionality.

3a

Like this post? Share it!