Multiple Text & Number String Extraction Without VBA

jawnathin

New Member
Joined
Nov 27, 2013
Messages
8
Alright Excel friends, I had a problem that was solved that has just grown once more. I need to find and extract multiple strings from a text field. These strings consist of two letters followed by three numbers and possibly an extra letter. It could be XX000 or XX000X (e.g. AB123 or BA123Z). There are hundreds (thousands?) of rows of data I need to grab these strings from.

The current array formula I'm using (provided by a member here) is:
=IF(ISBLANK(S3),MID(R3,MATCH(TRUE,ISNUMBER(1*MID(R3,ROW(INDIRECT("1:"&LEN(Q3))),1)),0)-2,5),S3)


Sample data below:

CreatedCommentDesired Extraction
1/1/2013Bananas BA426 Them more textBA426
5/1/2013Apples AP654 Bananas BA525 More additional textAP654 AND BA525
6/3/2013More Fruit MF055 AP162 BA223X Even more textMF055 AND AP162 AND BA223X
7/5/2013More Text Before Numbers MT323 and this one is going to be really long to prove a point. :wink:MT323

<tbody>
</tbody>

Any ideas?

My original thread is here: http://www.mrexcel.com/forum/excel-...action-without-visual-basic-applications.html

Thanks to everyone who checks this out! :)

____________
Jawnathin
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Matt Rogers

Well-known Member
Joined
Sep 23, 2011
Messages
1,030
Pup: honest compliment!

It's another way, but i've a problem with "X" ...

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='6' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='272,25pt'><col width='66,75pt'><col width='66,75pt'><col width='66,75pt'><col width='66,75pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='left' >Bananas BA426 Them more text</td><td align='middle' >BA426</td><td align='middle' style='color:#222222; ' > </td><td align='middle' style='color:#222222; ' > </td><td align='middle' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='left' >Apples AP654 Bananas BA525 More additional text</td><td align='middle' >AP654</td><td align='middle' style='color:#222222; ' >BA525</td><td align='middle' style='color:#222222; ' > </td><td align='middle' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='left' >More Fruit MF055 AP162 BA223X Even more text</td><td align='middle' >MF055</td><td align='middle' style='color:#222222; ' >AP162</td><td align='middle' style='color:#222222; ' >BA223</td><td align='middle' >BA223X</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='left' >More Text Before Numbers MT323 and…</td><td align='middle' >MT323</td><td align='middle' style='color:#222222; ' > </td><td align='middle' style='color:#222222; ' > </td><td align='middle' > </td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:fixed; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td><Span style='color:#222222'>{=MID</Span><Span style='color:#0000DD'>(A1,MATCH</Span><Span style='color:#222222'>(TRUE,ISNUMBER</Span><Span style='color:#0000DD'>(1*MID</Span><Span style='color:#222222'>(A1,ROW</Span><Span style='color:#0000DD'>(INDIRECT</Span><Span style='color:#222222'>("1:"&LEN</Span><Span style='color:#0000DD'>(A1)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>,1)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>,0)</Span><Span style='color:#0000DD'>-2,5)</Span><Span style='color:#222222'>}</Span></td></tr><tr><td>C1</td><td><Span style='color:#222222'>{=IFERROR</Span><Span style='color:#0000DD'>(MID</Span><Span style='color:#222222'>(SUBSTITUTE</Span><Span style='color:#0000DD'>($A1,$B1,"",1)</Span><Span style='color:#222222'>,MATCH</Span><Span style='color:#0000DD'>(TRUE,ISNUMBER</Span><Span style='color:#222222'>(1*MID</Span><Span style='color:#0000DD'>(SUBSTITUTE</Span><Span style='color:#222222'>($A1,$B1,"",1)</Span><Span style='color:#0000DD'>,ROW</Span><Span style='color:#222222'>($A$1:INDEX</Span><Span style='color:#0000DD'>($A:$A,LEN</Span><Span style='color:#222222'>(SUBSTITUTE</Span><Span style='color:#0000DD'>($A1,$B1,"",1)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>,1)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>,0)</Span><Span style='color:#222222'>-2,5)</Span><Span style='color:#0000DD'>,"")</Span><Span style='color:#222222'>}</Span></td></tr><tr><td>D1</td><td><Span style='color:#222222'>{=IFERROR</Span><Span style='color:#0000DD'>(MID</Span><Span style='color:#222222'>(SUBSTITUTE</Span><Span style='color:#0000DD'>(SUBSTITUTE</Span><Span style='color:#222222'>($A1,$B1,"",1)</Span><Span style='color:#0000DD'>,$C1,"",1)</Span><Span style='color:#222222'>,MATCH</Span><Span style='color:#0000DD'>(TRUE,ISNUMBER</Span><Span style='color:#222222'>(1*MID</Span><Span style='color:#0000DD'>(SUBSTITUTE</Span><Span style='color:#222222'>(SUBSTITUTE</Span><Span style='color:#0000DD'>($A1,$B1,"",1)</Span><Span style='color:#222222'>,$C1,"",1)</Span><Span style='color:#0000DD'>,ROW</Span><Span style='color:#222222'>($A$1:INDEX</Span><Span style='color:#0000DD'>($A:$A,LEN</Span><Span style='color:#222222'>(SUBSTITUTE</Span><Span style='color:#0000DD'>(SUBSTITUTE</Span><Span style='color:#222222'>($A1,$B1,"",1)</Span><Span style='color:#0000DD'>,$C1,"",1)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>,1)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>,0)</Span><Span style='color:#222222'>-2,5)</Span><Span style='color:#0000DD'>,"")</Span><Span style='color:#222222'>}</Span></td></tr><tr><td>E1</td><td><Span style='color:#222222'>=IFERROR</Span><Span style='color:#0000DD'>(MID</Span><Span style='color:#222222'>(A1,FIND</Span><Span style='color:#0000DD'>("X",A1)</Span><Span style='color:#222222'>-5,6)</Span><Span style='color:#0000DD'>,"")</Span><Span style='color:#222222'></Span></td></tr></table><table style='font-family:Arial; font-size:8pt; background-color:#FFFFFF'><tr><td style='font-weight:bold'>Array-Formula!</td></tr><tr><td><span>Ctrl+Shift + Enter </span><span style='text-decoration:underline'></span><span></span></td></tr><tr><td><span></span><span style='font-weight:bold; color:#FF0000'></span><span></span></td></tr></table>
 

Matt Rogers

Well-known Member
Joined
Sep 23, 2011
Messages
1,030
Slight change:

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='5' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='272,25pt'><col width='66,75pt'><col width='66,75pt'><col width='66,75pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='left' >Bananas BA426 Them more text</td><td align='middle' >BA426</td><td align='middle' style='color:#222222; ' > </td><td align='middle' style='color:#222222; ' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='left' >Apples AP654 Bananas BA525 More additional text</td><td align='middle' >AP654</td><td align='middle' style='color:#222222; ' >BA525</td><td align='middle' style='color:#222222; ' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='left' >More Fruit MF055 AP162 BA223X Even more text</td><td align='middle' >MF055</td><td align='middle' style='color:#222222; ' >AP162</td><td align='middle' style='color:#222222; ' >BA223X</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='left' >More Text Before Numbers MT323 and…</td><td align='middle' >MT323</td><td align='middle' style='color:#222222; ' > </td><td align='middle' style='color:#222222; ' > </td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:fixed; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td><Span style='color:#222222'>{=MID</Span><Span style='color:#0000DD'>(A1,MATCH</Span><Span style='color:#222222'>(TRUE,ISNUMBER</Span><Span style='color:#0000DD'>(1*MID</Span><Span style='color:#222222'>(A1,ROW</Span><Span style='color:#0000DD'>(INDIRECT</Span><Span style='color:#222222'>("1:"&LEN</Span><Span style='color:#0000DD'>(A1)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>,1)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>,0)</Span><Span style='color:#0000DD'>-2,6)</Span><Span style='color:#222222'>}</Span></td></tr><tr><td>C1</td><td><Span style='color:#222222'>{=IFERROR</Span><Span style='color:#0000DD'>(MID</Span><Span style='color:#222222'>(SUBSTITUTE</Span><Span style='color:#0000DD'>($A1,$B1,"",1)</Span><Span style='color:#222222'>,MATCH</Span><Span style='color:#0000DD'>(TRUE,ISNUMBER</Span><Span style='color:#222222'>(1*MID</Span><Span style='color:#0000DD'>(SUBSTITUTE</Span><Span style='color:#222222'>($A1,$B1,"",1)</Span><Span style='color:#0000DD'>,ROW</Span><Span style='color:#222222'>($A$1:INDEX</Span><Span style='color:#0000DD'>($A:$A,LEN</Span><Span style='color:#222222'>(SUBSTITUTE</Span><Span style='color:#0000DD'>($A1,$B1,"",1)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>,1)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>,0)</Span><Span style='color:#222222'>-2,6)</Span><Span style='color:#0000DD'>,"")</Span><Span style='color:#222222'>}</Span></td></tr><tr><td>D1</td><td><Span style='color:#222222'>{=IFERROR</Span><Span style='color:#0000DD'>(MID</Span><Span style='color:#222222'>(SUBSTITUTE</Span><Span style='color:#0000DD'>(SUBSTITUTE</Span><Span style='color:#222222'>($A1,$B1,"",1)</Span><Span style='color:#0000DD'>,$C1,"",1)</Span><Span style='color:#222222'>,MATCH</Span><Span style='color:#0000DD'>(TRUE,ISNUMBER</Span><Span style='color:#222222'>(1*MID</Span><Span style='color:#0000DD'>(SUBSTITUTE</Span><Span style='color:#222222'>(SUBSTITUTE</Span><Span style='color:#0000DD'>($A1,$B1,"",1)</Span><Span style='color:#222222'>,$C1,"",1)</Span><Span style='color:#0000DD'>,ROW</Span><Span style='color:#222222'>($A$1:INDEX</Span><Span style='color:#0000DD'>($A:$A,LEN</Span><Span style='color:#222222'>(SUBSTITUTE</Span><Span style='color:#0000DD'>(SUBSTITUTE</Span><Span style='color:#222222'>($A1,$B1,"",1)</Span><Span style='color:#0000DD'>,$C1,"",1)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>,1)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>,0)</Span><Span style='color:#222222'>-2,6)</Span><Span style='color:#0000DD'>,"")</Span><Span style='color:#222222'>}</Span></td></tr></table><table style='font-family:Arial; font-size:8pt; background-color:#FFFFFF'><tr><td style='font-weight:bold'>Array-Formula!</td></tr><tr><td><span>Ctrl+Shift + Enter </span><span style='text-decoration:underline'></span><span></span></td></tr><tr><td><span></span><span style='font-weight:bold; color:#FF0000'></span><span></span></td></tr></table>
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Alright Excel friends, I had a problem that was solved that has just grown once more. I need to find and extract multiple strings from a text field. These strings consist of two letters followed by three numbers and possibly an extra letter. It could be XX000 or XX000X (e.g. AB123 or BA123Z). There are hundreds (thousands?) of rows of data I need to grab these strings from.

The current array formula I'm using (provided by a member here) is:
=IF(ISBLANK(S3),MID(R3,MATCH(TRUE,ISNUMBER(1*MID(R3,ROW(INDIRECT("1:"&LEN(Q3))),1)),0)-2,5),S3)


Sample data below:

Created
Comment
Desired Extraction
1/1/2013
Bananas BA426 Them more text
BA426
5/1/2013
Apples AP654 Bananas BA525 More additional text
AP654 AND BA525
6/3/2013
More Fruit MF055 AP162 BA223X Even more text
MF055 AND AP162 AND BA223X
7/5/2013
More Text Before Numbers MT323 and this one is going to be really long to prove a point. :wink:
MT323

<TBODY>
</TBODY>

Any ideas?

My original thread is here: http://www.mrexcel.com/forum/excel-...action-without-visual-basic-applications.html

You will not be able to do that without using VBA. Here is a macro that will do it though...

Code:
Sub Codes()
  Dim X As Long, LastRow As Long, NextAND As Long, Cell As Range
  Dim Temp As String, Words() As String, ANDS() As String
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  Range("C2:C" & LastRow).Clear
  For Each Cell In Range("B2:B" & LastRow)
    Words = Split(Cell.Value)
    For X = 0 To UBound(Words)
      If Not Words(X) Like "[A-Za-z][A-Za-z]###" And Not Words(X) Like "[A-Za-z][A-Za-z]###[A-Za-z]" Then
        Words(X) = ""
      End If
    Next
    Temp = Replace(Application.Trim(Join(Words)), " ", " AND ")
    Cell.Offset(, 1).Value = Temp
    ANDS = Split(Temp, "AND")
    NextAND = 1 + Len(ANDS(0))
    For X = 0 To UBound(ANDS) - 1
      Cell.Offset(, 1).Characters(NextAND, 3).Font.Bold = True
      NextAND = NextAND + Len(ANDS(X + 1)) + 3
    Next
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the <ACRONYM title=vBulletin>VB</ACRONYM> editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Codes) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm).<!-- google_ad_section_end -->
 

Pup Denab

Active Member
Joined
May 12, 2013
Messages
299
Thanks Matt
I see you fixed the length issue, with X, very nice way to solve the problem, and oh some much smaller than my way :), it may have taken me, a lifetime to work that out lol
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Thanks Matt
I see you fixed the length issue, with X, very nice way to solve the problem, and oh some much smaller than my way :), it may have taken me, a lifetime to work that out lol
I am confused... are you saying Matt's solution answers your question? If "yes", then the reason I am confused is that Matt's code does not produce the output you said you wanted in your original message. The output you said you wanted had a specific format (the bolded word AND between each found number) and the output was all confined to a single cell per row whereas Matt's solution occupies from one to three cells. On top of that, his solution only handles a maximum of three numbers per data cell... if that data cell has four or more numbers in it, you will never know that with the solution Matt provided. Apologies to Matt if it sounds like I am trashing his solution... that is not my intent (it is a very good solution for what it does), it is just that you said you wanted one thing and appear to be saying something completely different is alright. If your needs were that flexible, you should have said so in your original message so others would not waste their time developing a solution you actually wanted only to have it ignored in favor of one that does not do what you asked for.
 

Pup Denab

Active Member
Joined
May 12, 2013
Messages
299
HI Rick
You are correct, it does not correctly match the requested format, however it is still a very close answer to the original request, without VBA (personally I agree with your answer but that wasn't the request, all we can do is answer the best we can & see if the asker is happy with that) a small change to Matt's answer, will make it in a single cell as below (all that is missing is the bold AND, and yes you are correct it only covers 3 possibility's)

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-2,6)&IFERROR(MID(SUBSTITUTE($A1,$C1,"",1),MATCH(TRUE,ISNUMBER(1*MID(SUBSTITUTE($A1,$C1,"",1),ROW($A$1:INDEX($A:$A,LEN(SUBSTITUTE($A1,$C1,"",1)))),1)),0)-2,6),"")&IFERROR(MID(SUBSTITUTE(SUBSTITUTE($A1,$C1,"",1),$D1,"",1),MATCH(TRUE,ISNUMBER(1*MID(SUBSTITUTE(SUBSTITUTE($A1,$C1,"",1),$D1,"",1),ROW($A$1:INDEX($A:$A,LEN(SUBSTITUTE(SUBSTITUTE($A1,$C1,"",1),$D1,"",1)))),1)),0)-2,6),"")

In Cell B1, then ctrl + shift + enter to make it an array formula
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
...however it is still a very close answer to the original request, without VBA...
Maybe. You are aware that his formula does not actually test for your required text format (two letters followed by three digits possibly followed by a letter) , correct? For example, test his code on these...

Bananas 12345 then more text

Bananas 12x12 Them more text

Bananas ABCD5 Them more text

and many, many more possibly non-compliant formats for the sought after text.
 

Matt Rogers

Well-known Member
Joined
Sep 23, 2011
Messages
1,030
The title is: Multiple Text & Number String Extraction Without VBA.

I'm waiting for original poster Jawnathin.
 

Forum statistics

Threads
1,144,582
Messages
5,725,110
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top