Multiple Text & Number String Extraction Without VBA

jawnathin · Dec 14, 2013

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:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Created[/TD]
[TD]Comment[/TD]
[TD]Desired Extraction[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]Bananas BA426 Them more text[/TD]
[TD]BA426[/TD]
[/TR]
[TR]
[TD]5/1/2013[/TD]
[TD]Apples AP654 Bananas BA525 More additional text[/TD]
[TD]AP654 AND BA525[/TD]
[/TR]
[TR]
[TD]6/3/2013[/TD]
[TD]More Fruit MF055 AP162 BA223X Even more text[/TD]
[TD]MF055 AND AP162 AND BA223X[/TD]
[/TR]
[TR]
[TD]7/5/2013[/TD]
[TD]More Text Before Numbers MT323 and this one is going to be really long to prove a point. :wink:[/TD]
[TD]MT323[/TD]
[/TR]
</tbody>[/TABLE]

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

Pup Denab · Dec 15, 2013

HI Their
I saw your thread and thought that's easy, what was i thinking, however you do ask some good questions, please see spreadsheet I have uploaded to dropbox, it seems a long way around to get what you need, but hopefully someone on here can cut it down

https://www.dropbox.com/s/t63wdnd1qa9h4a4/Extract codes alpha numeric .xlsx

Matt Rogers · Dec 15, 2013

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> <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>{=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-2,5)}</td></tr><tr><td>C1</td><td>{=IFERROR(MID(SUBSTITUTE($A1,$B1,"",1),MATCH(TRUE,ISNUMBER(1*MID(SUBSTITUTE($A1,$B1,"",1),ROW($A$1:INDEX($A:$A,LEN(SUBSTITUTE($A1,$B1,"",1)))),1)),0)-2,5),"")}</td></tr><tr><td>D1</td><td>{=IFERROR(MID(SUBSTITUTE(SUBSTITUTE($A1,$B1,"",1),$C1,"",1),MATCH(TRUE,ISNUMBER(1*MID(SUBSTITUTE(SUBSTITUTE($A1,$B1,"",1),$C1,"",1),ROW($A$1:INDEX($A:$A,LEN(SUBSTITUTE(SUBSTITUTE($A1,$B1,"",1),$C1,"",1)))),1)),0)-2,5),"")}</td></tr><tr><td>E1</td><td>=IFERROR(MID(A1,FIND("X",A1)-5,6),"")</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>Ctrl+Shift + Enter </td></tr><tr><td></td></tr></table>

Matt Rogers · Dec 15, 2013

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> <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>{=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-2,6)}</td></tr><tr><td>C1</td><td>{=IFERROR(MID(SUBSTITUTE($A1,$B1,"",1),MATCH(TRUE,ISNUMBER(1*MID(SUBSTITUTE($A1,$B1,"",1),ROW($A$1:INDEX($A:$A,LEN(SUBSTITUTE($A1,$B1,"",1)))),1)),0)-2,6),"")}</td></tr><tr><td>D1</td><td>{=IFERROR(MID(SUBSTITUTE(SUBSTITUTE($A1,$B1,"",1),$C1,"",1),MATCH(TRUE,ISNUMBER(1*MID(SUBSTITUTE(SUBSTITUTE($A1,$B1,"",1),$C1,"",1),ROW($A$1:INDEX($A:$A,LEN(SUBSTITUTE(SUBSTITUTE($A1,$B1,"",1),$C1,"",1)))),1)),0)-2,6),"")}</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>Ctrl+Shift + Enter </td></tr><tr><td></td></tr></table>

Rick Rothstein · Dec 15, 2013

jawnathin said:
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:

[TABLE="class: cms_table, width: 500"]
<TBODY>[TR]
[TD]Created
[/TD]
[TD]Comment
[/TD]
[TD]Desired Extraction
[/TD]
[/TR]
[TR]
[TD]1/1/2013
[/TD]
[TD]Bananas BA426 Them more text
[/TD]
[TD]BA426
[/TD]
[/TR]
[TR]
[TD]5/1/2013
[/TD]
[TD]Apples AP654 Bananas BA525 More additional text
[/TD]
[TD]AP654 AND BA525
[/TD]
[/TR]
[TR]
[TD]6/3/2013
[/TD]
[TD]More Fruit MF055 AP162 BA223X Even more text
[/TD]
[TD]MF055 AND AP162 AND BA223X
[/TD]
[/TR]
[TR]
[TD]7/5/2013
[/TD]
[TD]More Text Before Numbers MT323 and this one is going to be really long to prove a point. :wink:
[/TD]
[TD]MT323
[/TD]
[/TR]
</TBODY>[/TABLE]

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).

Pup Denab · Dec 16, 2013

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 · Dec 16, 2013

Pup Denab said:
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 · Dec 16, 2013

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 · Dec 16, 2013

Pup Denab said:
...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 · Dec 16, 2013

The title is: Multiple Text & Number String Extraction Without VBA.

I'm waiting for original poster Jawnathin.

Multiple Text & Number String Extraction Without VBA

jawnathin

New Member

Pup Denab

Active Member

Matt Rogers

Well-known Member

Matt Rogers

Well-known Member

Rick Rothstein

MrExcel MVP

Pup Denab

Active Member

Rick Rothstein

MrExcel MVP

Pup Denab

Active Member

Rick Rothstein

MrExcel MVP

Matt Rogers

Well-known Member

Similar threads

Share this page

Multiple Text & Number String Extraction Without VBA

New Member

Active Member

Well-known Member

Well-known Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock