Found string in text in column C and copy the founstring in adjacent cell in column D

    Hello,



    I'd like to scan a column C, with text, for a certain string that begins with "B0" folowed by 4 digits and put the found string (e.g. "B01574") in the adjacent cell, each time it finds the string. In this case, "B01574"
    The string can be anywhere in the text.
    The formula =If(ISERROR(SEARCH("B0****",C1,1)),"","B0****") is not working, when a B0 string is found, the result is B0****





    Thanks for the help



    aisietie

    Re: Found string in text in column C and copy the founstring in adjacent cell in colu


    If there are no numbers before the B0 string you could use this


    =MID(C2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C2&"0123456789"))-1,6)


    Otherwise I would use VBA, such as this custom function

    Code
    Function x(v As Variant) As String


With CreateObject("VBScript.RegExp")
    .Pattern = "B0[0-9]{4}"
    x = .Execute(v)(0)
End With


End Function

    Re: Found string in text in column C and copy the founstring in adjacent cell in colu


    Stephen,


    Thanks, but yes there can be numbers before the B0 string.....


    Aisietie

    Re: Found string in text in column C and copy the founstring in adjacent cell in colu


    Stephen,


    With the function it works allright.


    Thanks for that!!


    Aisietie

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login