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