LinkBack URL
About LinkBacks
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
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
Last edited by ARGK; 12-22-2013 at 05:33 PM.
ARGK,
Why not 2345??? If it is 2345 then here's a function for you:
Directions for running the routine(s) just supplied
Please Login or Register to view this content.
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
Last edited by xladept; 12-22-2013 at 05:32 PM.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Hi xladept
Well spotted, missed that one :-).
Tony
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
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Many thanks for the responses guys, I will try them over the holidays.
Kind regards
Tony
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.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
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}),""))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks