Excel number formatting on trim

I was recently trying to trim a page number from a journal title in some imported text data, to log in my articles database. I wanted Excel to automatically recognise the imported data as a number, and it wouldn't. I wanted a one step auto-function, rather than me having to apply another step to the text clean-up.

Grr.

However, a quick DuckDuckGo search found a nifty formatting formula (Exceltip, 2014) which we can apply alongside a trim: we specify value in the function. Even better, if we are after a standard number format, we don't even need to specify exactly what type of value we want.

Our formula looks like this: =VALUE(LEFT([cell reference],[number of characters we want to trim])); so for my purposes, this was:

=VALUE(LEFT(Q3,4))

And I got my page range, formatted as a number, all in one step.

Result!

Sam

References:

Exceltip. (2014, October 20). Make LEFT Function recognize a number in Excel. https://www.exceltip.com/excel-text-formulas/make-left-function-recognize-a-number.html