Extracting numbers from text strings


Newton Excel Bach, not (just) an Excel Blog

There is often a need to extract numbers from the start or end of text strings.  It’s not too hard to do with on-sheet formulas, but after having done it a few thousand times I decided it would be worth spending 5 minutes writing two VBA User Defined Functions (UDFs) to do the job.  Here they are:

NumRight() will extract a number from the right hand end of a text string, with the number delimited by a space by default, or optionally any other character:

Numleft() is similar, but extracts a number from the left hand end.

For both functions the input “NumStrings” may be either a single cell or a single column range. In the latter case the function must be entered as an array function to return all the results:

  • Enter the function, with the input range being a single column range
  • Select the range of output rows…

View original post 35 more words