Search a string within a sheet

capinss · Jan 26, 2009

Hello!
Probably an easy one for you guys...
I have a table of data and i need to search the table for 8digit numbers, that are beginning with "2" and copy the numbers to the column on the right. A cell can contain multiple numbers with 8 digits.
Thanks in advance.

Peter_SSs · Jan 26, 2009

Welcome to the MrExcel board!

Could you possibly provide a small set of sample data and expected results so we know just what the layout is?

A good way (but not the only way) to do that is to post a small screen shot with:

Excel jeanie
or
Colo's HTML Maker

Edit: Also, are the numbers actual numbers or numbers stored as text? Your thread title makes me wonder.

capinss · Jan 26, 2009

Sheet1

<table style="font-family: Tahoma,Arial; font-size: 8pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 139px;"><col style="width: 83px;"><col style="width: 53px;"><col style="width: 306px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="color: rgb(51, 102, 255);">As LIS Latgale</td><td style="color: rgb(51, 102, 255);">Viļānu </td><td style="color: rgb(51, 102, 255);">Rēzeknes </td><td style="color: rgb(51, 102, 255); text-align: right;">4662169</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="color: rgb(51, 102, 255);">Sia Sprūževa M</td><td style="color: rgb(51, 102, 255);">Griškānu </td><td style="color: rgb(51, 102, 255);">Rēzeknes </td><td style="color: rgb(51, 102, 255);"> Zoo Inta Silauniece-29334844, 4640429</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="color: rgb(51, 102, 255);"> Kruki SIA</td><td style="color: rgb(51, 102, 255);">Silmalas </td><td style="color: rgb(51, 102, 255);">Rēzeknes </td><td style="color: rgb(51, 102, 255);">29389342, 4645617</td></tr></tbody></table>
So, I want the number "29334844" to be copied to cell E2 and "29389342" to cell E3 ... and so on for a few thousand lines...

Peter_SSs · Jan 26, 2009

Hmm, I'm not sure I can think of a good way to do that at the moment. Hopefully somebody else will come along witha good idea. A couple of further questions to clarify though:

1. Can you confirm that the numbers would only be in column D?

2. Can you confirm that there is a maximum of 1 of these numbers per row?

capinss · Jan 26, 2009

1) The numbers will be only in column D
2) It is possible that in a cell there are multiple numbers.

Sal Paradise · Jan 26, 2009

A macro could replace all non-number characters with spaces, then reduce all multiple spaces to a single space, then check length of the remaining strings and populate things to the right with them. I can't think of a realistic formula solution...

Peter_SSs · Jan 26, 2009

capinss said:
2) It is possible that in a cell there are multiple numbers.

In that case, is this how you would want the result?

Excel Workbook

A

B

C

D

E

1

As LIS Latgale

Vilanu

Rezeknes

4662169

2

Sia Sprueva M

Grikanu

Rezeknes

Zoo Inta Silauniece-29334844, 4640429

29334844

3

Kruki SIA

Silmalas

Rezeknes

29389342, 4645617, 25698754

29389342, 25698754

Extract 8 digit number

capinss · Jan 26, 2009

Peter_SSs said:
In that case, is this how you would want the result?

Extract 8 digit number

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 112px;"><col style="width: 71px;"><col style="width: 78px;"><col style="width: 240px;"><col style="width: 162px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>As LIS Latgale</td><td>Vilanu</td><td>Rezeknes</td><td style="text-align: left;">4662169</td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>Sia Spruževa M</td><td>Griškanu</td><td>Rezeknes</td><td style="text-align: left;">Zoo Inta Silauniece-29334844, 4640429</td><td style="text-align: left;">29334844</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Kruki SIA</td><td>Silmalas</td><td>Rezeknes</td><td style="text-align: left;">29389342, 4645617, 25698754</td><td style="text-align: left;">29389342; 25698754</td></tr></tbody></table>
Yes, exactly, only seperated by semicolon, if there happens to be more than one number in the cell...

GTO · Jan 26, 2009

Greetings,

No promises, but in a copy of your workbook, try:

In a standard module:

Code:

Option Explicit
 Sub FindTwos()
Dim _
lRow                    As Long, _
rngToCheck              As Range, _
rCell                   As Range, _
strCell                 As String, _
aStrArray()             As Variant, _
i                       As Integer
  
     '// Presuming no phantom data, find last row//
    lRow = Sheet1.Cells(Rows.Count, 4).End(xlUp).Row
    
    '// Set range to check//
    Set rngToCheck = Sheet1.Range("D1:D" & lRow)
    
    '// For ea cell in range to check...//
    For Each rCell In rngToCheck

         '// Initially dimension array w/one empty element.  On all folowing //
        '// loops, clear array.                                             //
        ReDim aStrArray(0)
        
        '// If there are any 2's...//
        If Not InStr(1, rCell, 2, vbTextCompare) = 0 Then
            
            '// grab the cell...//
            strCell = rCell
            
            '// while there's still a 2 in our decreasing string...//
            Do While Not InStr(1, strCell, 2, vbTextCompare) = 0
            
                '// see if there's an eight characters with the 2 included//
                If Len(Mid(strCell, InStr(1, strCell, 2, vbTextCompare), 8)) = 8 Then

                     '// if yes, snag and palce in array//
                    aStrArray(UBound(aStrArray())) = _
                        Mid(strCell, InStr(1, strCell, 2, vbTextCompare), 8)

                     '// and add an element...//
                    ReDim Preserve aStrArray(UBound(aStrArray()) + 1)
                    
                    '// then strip to check remaining charaters.//
                    strCell = Right(strCell, (Len(strCell) - 7) - InStr(1, strCell, 2, vbTextCompare))
                    
                Else
                    Exit Do
                End If
            Loop
            
            '// If we found values, we have an extra element.  Rid it.//
            If Not UBound(aStrArray()) = 0 Then _
            ReDim Preserve aStrArray(UBound(aStrArray()) - 1)
            
            '// clear string//
            strCell = vbNullString
            
            '// If first element has a value, we found stuff, so...//
            If Not aStrArray(0) = Empty Then

                 '// for ea value found, add to string w/a semi-colon appended//
                For i = LBound(aStrArray()) To UBound(aStrArray())
                    strCell = strCell & aStrArray(i) & ";"
                Next
                    
                '// tear off last semi-colon//
                strCell = Left(strCell, Len(strCell) - 1)

                 '// add string to E//
                rCell.Offset(, 1).Value = strCell
                
            End If
        End If
    Next
End Sub

NOTES: I used the sheet's codename, so change to match.

Hope this helps,

Mark

JB_Scotland · Jan 26, 2009

You could using the SEARCH function and search for 2??????? where the ? in a character wildcard.

You need to discount mixed text and numbers (e.g. "2 The Li") so the formula checks for numbers and the remaining data (less what was found) is used as a basis for the next search. The extracted data is then joined together in column T.

A bit long winded, but it works.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 110px"><COL style="WIDTH: 62px"><COL style="WIDTH: 68px"><COL style="WIDTH: 297px"><COL style="WIDTH: 80px"><COL style="WIDTH: 161px"><COL style="WIDTH: 80px"><COL style="WIDTH: 161px"><COL style="WIDTH: 80px"><COL style="WIDTH: 161px"><COL style="WIDTH: 80px"><COL style="WIDTH: 161px"><COL style="WIDTH: 80px"><COL style="WIDTH: 161px"><COL style="WIDTH: 80px"><COL style="WIDTH: 161px"><COL style="WIDTH: 80px"><COL style="WIDTH: 161px"><COL style="WIDTH: 80px"><COL style="WIDTH: 198px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Data</TD><TD style="FONT-WEIGHT: bold">Data</TD><TD style="FONT-WEIGHT: bold">Data</TD><TD style="FONT-WEIGHT: bold">Search Data</TD><TD style="FONT-WEIGHT: bold">Result</TD><TD style="FONT-WEIGHT: bold">Remaining Search Data</TD><TD style="FONT-WEIGHT: bold">Result</TD><TD style="FONT-WEIGHT: bold">Remaining Search Data</TD><TD style="FONT-WEIGHT: bold">Result</TD><TD style="FONT-WEIGHT: bold">Remaining Search Data</TD><TD style="FONT-WEIGHT: bold">Result</TD><TD style="FONT-WEIGHT: bold">Remaining Search Data</TD><TD style="FONT-WEIGHT: bold">Result</TD><TD style="FONT-WEIGHT: bold">Remaining Search Data</TD><TD style="FONT-WEIGHT: bold">Result</TD><TD style="FONT-WEIGHT: bold">Remaining Search Data</TD><TD style="FONT-WEIGHT: bold">Result</TD><TD style="FONT-WEIGHT: bold">Remaining Search Data</TD><TD style="FONT-WEIGHT: bold">Result</TD><TD style="FONT-WEIGHT: bold">Extracted Numbers</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana">As LIS Latgale</TD><TD style="FONT-FAMILY: Verdana">Vilanu</TD><TD style="FONT-FAMILY: Verdana">Rezeknes</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">4662169</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">662169</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">62169</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">2169</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">169</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">69</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">9</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana">Sia Spruževa M</TD><TD style="FONT-FAMILY: Verdana">Griškanu</TD><TD style="FONT-FAMILY: Verdana">Rezeknes</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">Zoo Inta Silauniece-29334844, 4640429</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">29334844; </TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">, 4640429</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">4640429</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">4640429</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">640429</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">40429</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">0429</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">429</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: right">29334844</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana">Kruki SIA</TD><TD style="FONT-FAMILY: Verdana">Silmalas</TD><TD style="FONT-FAMILY: Verdana">Rezeknes</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">29389342, 4645617, 25698754</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">29389342; </TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">, 4645617, 25698754</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">25698754; </TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">#VALUE!</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">#VALUE!</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">#VALUE!</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">#VALUE!</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">#VALUE!</TD><TD style="FONT-FAMILY: Verdana"></TD><TD>29389342; 25698754</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=IF(ISERROR(VALUE(MID(D2,SEARCH("2???????",D2),8))),"",VALUE(MID(D2,SEARCH("2???????",D2),8))&"; ")</TD></TR><TR><TD>F2</TD><TD>=IF(E2="",RIGHT(D2,LEN(D2)-1),RIGHT(D2,LEN(D2)-SEARCH("2???????",D2)-7))</TD></TR><TR><TD>G2</TD><TD>=IF(ISERROR(VALUE(MID(F2,SEARCH("2???????",F2),8))),"",VALUE(MID(F2,SEARCH("2???????",F2),8))&"; ")</TD></TR><TR><TD>H2</TD><TD>=IF(G2="",RIGHT(F2,LEN(F2)-1),RIGHT(F2,LEN(F2)-SEARCH("2???????",F2)-7))</TD></TR><TR><TD>I2</TD><TD>=IF(ISERROR(VALUE(MID(H2,SEARCH("2???????",H2),8))),"",VALUE(MID(H2,SEARCH("2???????",H2),8))&"; ")</TD></TR><TR><TD>J2</TD><TD>=IF(I2="",RIGHT(H2,LEN(H2)-1),RIGHT(H2,LEN(H2)-SEARCH("2???????",H2)-7))</TD></TR><TR><TD>K2</TD><TD>=IF(ISERROR(VALUE(MID(J2,SEARCH("2???????",J2),8))),"",VALUE(MID(J2,SEARCH("2???????",J2),8))&"; ")</TD></TR><TR><TD>L2</TD><TD>=IF(K2="",RIGHT(J2,LEN(J2)-1),RIGHT(J2,LEN(J2)-SEARCH("2???????",J2)-7))</TD></TR><TR><TD>M2</TD><TD>=IF(ISERROR(VALUE(MID(L2,SEARCH("2???????",L2),8))),"",VALUE(MID(L2,SEARCH("2???????",L2),8))&"; ")</TD></TR><TR><TD>N2</TD><TD>=IF(M2="",RIGHT(L2,LEN(L2)-1),RIGHT(L2,LEN(L2)-SEARCH("2???????",L2)-7))</TD></TR><TR><TD>O2</TD><TD>=IF(ISERROR(VALUE(MID(N2,SEARCH("2???????",N2),8))),"",VALUE(MID(N2,SEARCH("2???????",N2),8))&"; ")</TD></TR><TR><TD>P2</TD><TD>=IF(O2="",RIGHT(N2,LEN(N2)-1),RIGHT(N2,LEN(N2)-SEARCH("2???????",N2)-7))</TD></TR><TR><TD>Q2</TD><TD>=IF(ISERROR(VALUE(MID(P2,SEARCH("2???????",P2),8))),"",VALUE(MID(P2,SEARCH("2???????",P2),8))&"; ")</TD></TR><TR><TD>R2</TD><TD>=IF(Q2="",RIGHT(P2,LEN(P2)-1),RIGHT(P2,LEN(P2)-SEARCH("2???????",P2)-7))</TD></TR><TR><TD>S2</TD><TD>=IF(ISERROR(VALUE(MID(R2,SEARCH("2???????",R2),8))),"",VALUE(MID(R2,SEARCH("2???????",R2),8))&"; ")</TD></TR><TR><TD>T2</TD><TD>=IF(LEN(E2&G2&I2&K2&M2&O2&Q2&S2)<>0,LEFT(E2&G2&I2&K2&M2&O2&Q2&S2,LEN(E2&G2&I2&K2&M2&O2&Q2&S2)-1),"")</TD></TR><TR><TD>E3</TD><TD>=IF(ISERROR(VALUE(MID(D3,SEARCH("2???????",D3),8))),"",VALUE(MID(D3,SEARCH("2???????",D3),8))&"; ")</TD></TR><TR><TD>F3</TD><TD>=IF(E3="",RIGHT(D3,LEN(D3)-1),RIGHT(D3,LEN(D3)-SEARCH("2???????",D3)-7))</TD></TR><TR><TD>G3</TD><TD>=IF(ISERROR(VALUE(MID(F3,SEARCH("2???????",F3),8))),"",VALUE(MID(F3,SEARCH("2???????",F3),8))&"; ")</TD></TR><TR><TD>H3</TD><TD>=IF(G3="",RIGHT(F3,LEN(F3)-1),RIGHT(F3,LEN(F3)-SEARCH("2???????",F3)-7))</TD></TR><TR><TD>I3</TD><TD>=IF(ISERROR(VALUE(MID(H3,SEARCH("2???????",H3),8))),"",VALUE(MID(H3,SEARCH("2???????",H3),8))&"; ")</TD></TR><TR><TD>J3</TD><TD>=IF(I3="",RIGHT(H3,LEN(H3)-1),RIGHT(H3,LEN(H3)-SEARCH("2???????",H3)-7))</TD></TR><TR><TD>K3</TD><TD>=IF(ISERROR(VALUE(MID(J3,SEARCH("2???????",J3),8))),"",VALUE(MID(J3,SEARCH("2???????",J3),8))&"; ")</TD></TR><TR><TD>L3</TD><TD>=IF(K3="",RIGHT(J3,LEN(J3)-1),RIGHT(J3,LEN(J3)-SEARCH("2???????",J3)-7))</TD></TR><TR><TD>M3</TD><TD>=IF(ISERROR(VALUE(MID(L3,SEARCH("2???????",L3),8))),"",VALUE(MID(L3,SEARCH("2???????",L3),8))&"; ")</TD></TR><TR><TD>N3</TD><TD>=IF(M3="",RIGHT(L3,LEN(L3)-1),RIGHT(L3,LEN(L3)-SEARCH("2???????",L3)-7))</TD></TR><TR><TD>O3</TD><TD>=IF(ISERROR(VALUE(MID(N3,SEARCH("2???????",N3),8))),"",VALUE(MID(N3,SEARCH("2???????",N3),8))&"; ")</TD></TR><TR><TD>P3</TD><TD>=IF(O3="",RIGHT(N3,LEN(N3)-1),RIGHT(N3,LEN(N3)-SEARCH("2???????",N3)-7))</TD></TR><TR><TD>Q3</TD><TD>=IF(ISERROR(VALUE(MID(P3,SEARCH("2???????",P3),8))),"",VALUE(MID(P3,SEARCH("2???????",P3),8))&"; ")</TD></TR><TR><TD>R3</TD><TD>=IF(Q3="",RIGHT(P3,LEN(P3)-1),RIGHT(P3,LEN(P3)-SEARCH("2???????",P3)-7))</TD></TR><TR><TD>S3</TD><TD>=IF(ISERROR(VALUE(MID(R3,SEARCH("2???????",R3),8))),"",VALUE(MID(R3,SEARCH("2???????",R3),8))&"; ")</TD></TR><TR><TD>T3</TD><TD>=IF(LEN(E3&G3&I3&K3&M3&O3&Q3&S3)<>0,LEFT(E3&G3&I3&K3&M3&O3&Q3&S3,LEN(E3&G3&I3&K3&M3&O3&Q3&S3)-2),"")</TD></TR><TR><TD>E4</TD><TD>=IF(ISERROR(VALUE(MID(D4,SEARCH("2???????",D4),8))),"",VALUE(MID(D4,SEARCH("2???????",D4),8))&"; ")</TD></TR><TR><TD>F4</TD><TD>=IF(E4="",RIGHT(D4,LEN(D4)-1),RIGHT(D4,LEN(D4)-SEARCH("2???????",D4)-7))</TD></TR><TR><TD>G4</TD><TD>=IF(ISERROR(VALUE(MID(F4,SEARCH("2???????",F4),8))),"",VALUE(MID(F4,SEARCH("2???????",F4),8))&"; ")</TD></TR><TR><TD>H4</TD><TD>=IF(G4="",RIGHT(F4,LEN(F4)-1),RIGHT(F4,LEN(F4)-SEARCH("2???????",F4)-7))</TD></TR><TR><TD>I4</TD><TD>=IF(ISERROR(VALUE(MID(H4,SEARCH("2???????",H4),8))),"",VALUE(MID(H4,SEARCH("2???????",H4),8))&"; ")</TD></TR><TR><TD>J4</TD><TD>=IF(I4="",RIGHT(H4,LEN(H4)-1),RIGHT(H4,LEN(H4)-SEARCH("2???????",H4)-7))</TD></TR><TR><TD>K4</TD><TD>=IF(ISERROR(VALUE(MID(J4,SEARCH("2???????",J4),8))),"",VALUE(MID(J4,SEARCH("2???????",J4),8))&"; ")</TD></TR><TR><TD>L4</TD><TD>=IF(K4="",RIGHT(J4,LEN(J4)-1),RIGHT(J4,LEN(J4)-SEARCH("2???????",J4)-7))</TD></TR><TR><TD>M4</TD><TD>=IF(ISERROR(VALUE(MID(L4,SEARCH("2???????",L4),8))),"",VALUE(MID(L4,SEARCH("2???????",L4),8))&"; ")</TD></TR><TR><TD>N4</TD><TD>=IF(M4="",RIGHT(L4,LEN(L4)-1),RIGHT(L4,LEN(L4)-SEARCH("2???????",L4)-7))</TD></TR><TR><TD>O4</TD><TD>=IF(ISERROR(VALUE(MID(N4,SEARCH("2???????",N4),8))),"",VALUE(MID(N4,SEARCH("2???????",N4),8))&"; ")</TD></TR><TR><TD>P4</TD><TD>=IF(O4="",RIGHT(N4,LEN(N4)-1),RIGHT(N4,LEN(N4)-SEARCH("2???????",N4)-7))</TD></TR><TR><TD>Q4</TD><TD>=IF(ISERROR(VALUE(MID(P4,SEARCH("2???????",P4),8))),"",VALUE(MID(P4,SEARCH("2???????",P4),8))&"; ")</TD></TR><TR><TD>R4</TD><TD>=IF(Q4="",RIGHT(P4,LEN(P4)-1),RIGHT(P4,LEN(P4)-SEARCH("2???????",P4)-7))</TD></TR><TR><TD>S4</TD><TD>=IF(ISERROR(VALUE(MID(R4,SEARCH("2???????",R4),8))),"",VALUE(MID(R4,SEARCH("2???????",R4),8))&"; ")</TD></TR><TR><TD>T4</TD><TD>=IF(LEN(E4&G4&I4&K4&M4&O4&Q4&S4)<>0,LEFT(E4&G4&I4&K4&M4&O4&Q4&S4,LEN(E4&G4&I4&K4&M4&O4&Q4&S4)-2),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Search a string within a sheet

capinss

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

capinss

New Member

Peter_SSs

MrExcel MVP, Moderator

capinss

New Member

Sal Paradise

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

capinss

New Member

GTO

MrExcel MVP

JB_Scotland

Board Regular

Similar threads

Forum statistics

Share this page

Search a string within a sheet

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

Well-known Member

MrExcel MVP, Moderator

New Member

MrExcel MVP

Board Regular

Similar threads

Forum statistics

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