x
Solved

# extract text

Posted on 2014-03-11
Medium Priority
391 Views
I need a formula that will extract the text from a string with numbers preceding and following the text. i.e.
1C3, 12B4, 23STR6,
there may be 1 to 4 numbers preceding and following and 1 to 4 text characters.
It may also have text preceding the number like STR4 or G7.
I need the formula to be in say cell B1 and the string to be in A1.
A1 would have 12STR23 ant the result would be in B1 STR.
thanks Bill
0
Question by:Billkronmiller

LVL 37

Expert Comment

ID: 39922334
Using your own function, you can achieve it, as explained here:

http://www.mrexcel.com/forum/excel-questions/16364-how-remove-numbers.html
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 680 total points
ID: 39922343
It's a little long but this array formula should work:

=MID(A1,MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0),IFERROR(MATCH(FALSE,ISERROR(VALUE(MID(A1,ROW(INDIRECT(MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)&":"&LEN(A1))),1))),0)+MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)-1-MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0),LEN(A1)-MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)+1))

Enter by pressing CTRL+SHIFT+ENTER.

Kevin
0

LVL 39

Expert Comment

ID: 39922357
You can also copy the attached regexpfind code from Patrick Matthews in a module of your workbook and call it in your worksheet as follows.

=regexpfind(A1,"\D+",1)

Read the article too, it's great.

Thomas

``````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

' 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
``````
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.