Multiple Text & Number String Extraction Without VBA

[jawnathin]

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

 

[jawnathin]

That is probably because your data is not in the cell range I assumed it would be in (you did not tell us where it is in your original message). So, where is your data... what column are they in and what row contains your first piece of data (I'll modify the code to adjust around it)?

Good point Rick - thanks. The Summary column is in Column R and data starts in Row 2.

 

[Rick Rothstein]

Good point Rick - thanks. The Summary column is in Column R and data starts in Row 2.

See if this code works for you...

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, "R").End(xlUp).Row
  Range("S2:S" & LastRow).Clear
  For Each Cell In Range("R2:R" & LastRow)
    Words = Split(Replace(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)), " ", "; ")
    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

 

[Pup Denab]

HI Jawnathin
Rick has probably worked his magic again, however I have had time to rethink you issue, it can be done with formula's, please see attached sheet, just paste some different data in column A, i.e. city and codes, I'm 99% sure it will return the correct result with the limitation of only 5 codes, and the strange double listing of cities, i.e. South Pasadena San Marino, as the same ref, but please try, as I've tried hard to meet your requirements and would like you opinion
Thanks
Pup

https://www.dropbox.com/s/liknko8z8i9btpv/Searchandfind.xlsx

 

[Naeven]

See if this code works for you...

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, "R").End(xlUp).Row
  Range("S2:S" & LastRow).Clear
  For Each Cell In Range("R2:R" & LastRow)
    Words = Split(Replace(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)), " ", "; ")
    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

Good day,
I Need a script/macro almost exacly like this, to break out order numbers from lots of free text on every row.
But it should search in column E and search for a format that is 8 digits Long (and always starts with a "1") - and modifying the macro is beyond me.

Example cell in column E: "12345678 AB1234/Name FirstName, 12345678, AB1234/Name FirstName, 12345678 AB1234/Name Firstname, bla bla bla"
Resulting in: "12345678 , 12345678 , 12345678"

Could Rick or anyone help me modify it to do above?
Thanks in advance.