Extract Numbers from Alphanumeric String

OTPM · #1 05-12-2011, 01:40 AM

Hi All
I am looking for a formula that will extract the first number from an alphanumeric string that may have more than one number contained inside the string. For example I want to be able to extract "360" only from the following alphanumeric string:

360FS+6wks

Any help would be appreciated.
Tony

macropod · #2 05-12-2011, 04:24 AM

Hi Tony,

Is there any consistency in the data, such a the '+'?

OTPM · #3 05-12-2011, 05:14 AM

Hi Macropod
Unfortunately not. The varying examples are as follows:
2FS+3 wks
22FS+3 wks
222FS+3 wks etc
Tony

macropod · #4 05-12-2011, 06:42 AM

Hi Tony,

Your reply suggests all the data do have a '+' and, apparently, a 'F' immediately after the number. Is either of these so?

OTPM · #5 05-12-2011, 06:45 AM

Hi Macropod
Each entry may either have an "+" or a "-" and it will either be an F or an S.

Colin Legg · #6 05-12-2011, 06:51 AM

If the number is always immediately followed by an "F" or an "S", then you could use this formula:

Code:

=LEFT(A1,MIN(SEARCH({"F","S"},A1&"FS")-1))

OTPM · #7 05-13-2011, 12:52 AM

Quote:

Originally Posted by Colin Legg

If the number is always immediately followed by an "F" or an "S", then you could use this formula:

Code:

=LEFT(A1,MIN(SEARCH({"F","S"},A1&"FS")-1))

Hi Colin
Brilliant. It works fine (better than the formula I used that tok me about 2 hours to work out :-)).
Thanks for your help.
Tony

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to extract only numbers from a STRING?	Learner7	Excel	3	07-02-2013 06:25 AM
Can't See Attachments When E-mail String is Saved to PDF	wineattorney	Outlook	1	03-29-2011 02:15 AM
Extract numbers from a text string	aleale97	Excel	4	02-10-2011 10:33 AM
inserting a string of data into an MS Word table???	matto	Word VBA	0	07-16-2010 09:35 AM
Extract from String using Wildcard	whousedmy	Word	0	05-21-2009 01:35 AM