Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 232

# Left Excel Funciton to extract text

Dear Excel Gurus,
I need a function to extract text.  Please see my screen shot and attached example.

Thanks
FunctionExample.xlsx
0
BajanPaul
• 3
• 3
• 2
• +2
1 Solution

Commented:
BajanPaul,

What are the rules for the Part No. (or for the part of Product that isn't the Part No.)?

Thanks,
Brian.
0

Commented:
Hello BajanPaul,

Assuming all part numbers have at least 6 characters try this formula in A2

=LEFT(B2,FIND(" ",B2,6))

Assuming "Product" in B2

regards, barry
0

Commented:
either:
=LEFT(\$B:\$B;FIND(" ";\$B:\$B)-1)
or:
=LEFT(\$B:\$B;FIND("  ";\$B:\$B)-1)

since you seem to have one space in part numbers such as line 12.
0

Commented:

=IFERROR(LEFT(B3,FIND("  ",B3,1)-1),LEFT(B3,FIND(" ",B3,1)-1))

So, it looks for two spaces and if it doesn't find that it uses one.

Regards,
Brian.
0

Commented:
Sorry, I should have subtracted 1 otherwise you get a trailing space....

=LEFT(B2,FIND(" ",B2,6)-1)

barry
0

Commented:
In looking at the file...

Most rows seem to be breaking at the point where there are 2+ consecutive spaces.  However, Row 8 shows a break at a singe space.

The rule does not appear to be "break atthe first space", because Rows 3 & 4 violate that.

The following Regular Expressions-powered formula is my best guess at it:

=RegExpFind(B3,"^[^ ]+( [^ ]+)*(?=( {2,}| [^ ]+\$))",1)

To use that, you'll need to add this function to a regular VBA module:

``````Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
Optional MatchCase As Boolean = True, Optional ReturnType As Long = 0, _
Optional MultiLine As Boolean = False)

' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
' as long as you properly credit and attribute authorship and the URL of where you
' found the code

' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

' This function relies on the VBScript version of Regular Expressions, and thus some of
' the functionality available in Perl and/or .Net may not be available.  The full extent
' of what functionality will be available on any given computer is based on which version
' of the VBScript runtime is installed on that computer

' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
' pattern (PatternStr).  Use Pos to indicate which match you want:
' Pos omitted               : function returns a zero-based array of all matches
' Pos = 1                   : the first match
' Pos = 2                   : the second match
' Pos = <positive integer>  : the Nth match
' Pos = 0                   : the last match
' Pos = -1                  : the last match
' Pos = -2                  : the 2nd to last match
' Pos = <negative integer>  : the Nth to last match
' If Pos is non-numeric, or if the absolute value of Pos is greater than the number of
' matches, the function returns an empty string.  If no match is found, the function returns
' an empty string.  (Earlier versions of this code used zero for the last match; this is
' retained for backward compatibility)

' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).

' ReturnType indicates what information you want to return:
' ReturnType = 0            : the matched values
' ReturnType = 1            : the starting character positions for the matched values
' ReturnType = 2            : the lengths of the matched values

' If you use this function in Excel, you can use range references for any of the arguments.
' If you use this in Excel and return the full array, make sure to set up the formula as an
' array formula.  If you need the array formula to go down a column, use TRANSPOSE()

' Note: RegExp counts the character positions for the Match.FirstIndex property as starting
' at zero.  Since VB6 and VBA has strings starting at position 1, I have added one to make
' the character positions conform to VBA/VB6 expectations

' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
' where a large number of calls to this function are made, making RegX a static variable that
' preserves its state in between calls significantly improves performance

Static RegX As Object
Dim TheMatches As Object
Dim Counter As Long

' Evaluate Pos.  If it is there, it must be numeric and converted to Long

If Not IsMissing(Pos) Then
If Not IsNumeric(Pos) Then
RegExpFind = ""
Exit Function
Else
Pos = CLng(Pos)
End If
End If

' Evaluate ReturnType

If ReturnType < 0 Or ReturnType > 2 Then
RegExpFind = ""
Exit Function
End If

' Create instance of RegExp object if needed, and set properties

If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Pattern = PatternStr
.Global = True
.IgnoreCase = Not MatchCase
.MultiLine = MultiLine
End With

' Test to see if there are any matches

If RegX.Test(LookIn) Then

' Run RegExp to get the matches, which are returned as a zero-based collection

Set TheMatches = RegX.Execute(LookIn)

' Test to see if Pos is negative, which indicates the user wants the Nth to last
' match.  If it is, then based on the number of matches convert Pos to a positive
' number, or zero for the last match

If Not IsMissing(Pos) Then
If Pos < 0 Then
If Pos = -1 Then
Pos = 0
Else

' If Abs(Pos) > number of matches, then the Nth to last match does not
' exist.  Return a zero-length string

If Abs(Pos) <= TheMatches.Count Then
Pos = TheMatches.Count + Pos + 1
Else
RegExpFind = ""
GoTo Cleanup
End If
End If
End If
End If

' If Pos is missing, user wants array of all matches.  Build it and assign it as the
' function's return value

If IsMissing(Pos) Then
ReDim Answer(0 To TheMatches.Count - 1)
For Counter = 0 To UBound(Answer)
Select Case ReturnType
Case 1: Answer(Counter) = TheMatches(Counter).FirstIndex + 1
End Select
Next

' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible

Else
Select Case Pos
Case 0                          ' Last match
Select Case ReturnType
Case 0: RegExpFind = TheMatches(TheMatches.Count - 1)
Case 1: RegExpFind = TheMatches(TheMatches.Count - 1).FirstIndex + 1
Case 2: RegExpFind = TheMatches(TheMatches.Count - 1).Length
End Select
Case 1 To TheMatches.Count      ' Nth match
Select Case ReturnType
Case 0: RegExpFind = TheMatches(Pos - 1)
Case 1: RegExpFind = TheMatches(Pos - 1).FirstIndex + 1
Case 2: RegExpFind = TheMatches(Pos - 1).Length
End Select
Case Else                       ' Invalid item number
RegExpFind = ""
End Select
End If

' If there are no matches, return empty string

Else
RegExpFind = ""
End If

Cleanup:
' Release object variables

Set TheMatches = Nothing

End Function
``````

The attached file demonstrates my approach.
Q-27391616.xlsm
0

Commented:
Translating the pattern string "^[^ ]+( [^ ]+)*(?=( {2,}| [^ ]+\$))" ...

^ is the beginning of input

[^ ]+ is one or more non-spaces

( [^ ]+)* is 0 or more instances of a single space followed by one or more non-spaces

(?=( {2,}| [^ ]+\$)) is a positive lookahead.  It's saying that the above expression must be followed by either 2 or more spaces, or by a single space followed by one or more non-spaces followed by the end of input.  In a positive lookahead, the pattern must match, but that portion is not included in the returned match.

0

Commented:
Sorry, I still got my suggestion wrong, third time lucky - assuming part numbers have at least 6 characters

=LEFT(B2,FIND(" ",B2,7)-1)

I believe that works for all your examples takes everything that comes before the first space after character 6 - that might not completely suit your requirements

barry
0

Author Commented:
Worked Perfectly.

Thanks
0

Commented:
Thanks, BajanPaul!
0

## Featured Post

• 3
• 3
• 2
• +2
Tackle projects and never again get stuck behind a technical roadblock.