Excel 2010 - Extract text between spaces in a string

[cpjeffm]

Need some help with this one. I have a string in cell E2 that I need to extract everything between the first two spaces. The string is...

INV 7243-1 FRG LH (Extra Sale) ANY

I need to extract 7243-1 from this string but here's the kicker. It won't always be 4 digits, a hyphen and 1 digit. It could be 2 digits, a hyphen and 1 digit, or 5 digits, a hyphen and two digits, etc so the only way I know to do this where it will work is to somehow use the space before and after.

Thanks in advance!!!

 

[SkipVought]

Hi,

Quickest would be Data > Text to columns...Delimited (using space)

Only 'import' the second column.

 

[cpjeffm]

I need to extract the data from a cell in a queried table. Not sure how I would use the text to columns for this.

 

[cpjeffm]

I got it using the following I found on

http://www.extendoffice.com/documents/excel/1767-excel-extract-between-commas.html.

Extract text between commas/space by using Defined Function

In addition to formula, you also can use Defined Function to extract text between commas, spaces in Excel.

1. Press Alt + F11 to display the Microsoft Visual Basic for Applications window.
2. In the window, click Insert > Module to show a new module window, then copy the following VBA code into the module window.

Function SplitString(pValue As String, pChar As String, pIndex As Integer) As Variant
'Updateby20140612
SplitString = Split(pValue, pChar)(pIndex - 1)
End Function

3. Save this code and then select a cell to type this formula =SplitString(A1,",",2) into it, and the press Enter button on the keyboard. Then the text before the second comma is extracted.

 

[SkipVought]

Well this is what happens when you disclose only a sliver of relevant information.

So in a query, You'ld use a combination of MID(), INSTR()