Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# extract text

Posted on 2014-03-11
Medium Priority
370 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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 36

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.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month7 days, 20 hours left to enroll