Extract the first Number from a Text String

[ARGK]

Hi
I need some help in extracting a number from the beginning of a text string. For example:
465FS+8weeks should return 465
34SS-3days should return 34
2345FF-7wks should return 2345
Any help appreciated.
Kind regards
Tony

[xladept]

ARGK,

Why not 2345??? If it is 2345 then here's a function for you:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

On the Spreadsheet type "=TopNo(" Select the cell with the data, close the parentheses.

And, you should be ready to go

[ARGK]

Hi xladept
Well spotted, missed that one :-).
Tony

[FDibbins]

Try this....
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre

[ARGK]

Many thanks for the responses guys, I will try them over the holidays.
Kind regards
Tony

[Tony Valko]

Will any of the numbers start with leading zeros?

00123

Is there a limit as to how long the number string will be? For example, the longest string will not exceed 6 digits.

[István Hirsch]

Try this if the numbers to extract are not longer than 12 digits; enter it with Shift-Ctrl-Enter into B1:

=MAX(IFERROR(--MID(A1,1,{1,2,3,4,5,6,7,8,9,10,11,12}),""))