Extracting String From Memo Field

[mr moe]

Hi I have a memo field that contains model ID and model description and blank spaces ect... the data doesnt have the same formate in each raw, it's kind of messy, I'm trying to extract the model ID it's 8 characters long. Can anybody suggest a way? if possible do you guys know the function in excel as well. Thanks.

 

[Brianwarnock]

We need more than the strings length as we need t o know how to recognise it.

Brian

 

[mr moe]

Can't we recognise it by the text lenght, like anything that is 8 characters move it to a different column? I'm still trying to find another way to recognise it maybe my the first 3 characters or something but the first 3 characters are never the same!! man this is not easy.

 

[mr moe]

Ok lets assume that the model id starts with either ABC or DDC or FMC ect. in this case we have two ways to recognise it by either the first 3 starting character or by the character lenght of the whole text.

 

[spikepl]

If your model ID has some specifcs that no other strings in the text contain, then you can use regular expressions to find it. Search the forum for regular expressions

 

[Brianwarnock]

Are you saying that that is the only string in the memo field that can be 8 characters long? If so I wonder if we create an array from the field we can check to find the cell that is 8 charters long, just museing here.

Brian

 

[mr moe]

Well the easiet way is to look for all words with 8 characters long and extract them into a seperate field. Then if i run into problems i have to approach it in a different way for example start at teh first three characters to see if they match a list of models then extract. But first I would like to only using the simpler method by only extacting values that have 8 characters. Can you help?

 

[Brianwarnock]

Unfortunately being a 24/7 carer for my wife I don't have a lot of time nor do I know when I have that time, however if you read up on the Split function and Ubound it will help

The code will I think, be like this

Function myfunc(myfield)
dim n as long
n = 0

myarray = Split(myfield, " ")

do until n = uBound(Myarray)
  If Len(myarray(n)) =8 then
   at this point we have found an 8 character word, perhaps you could now check to see if it is a modelID based on your list or concatenate it with others in Myfunc until you have serched the array and returned them to the query (or cell in Excel)

myfunc = myfunc & " " & myarray(n) 
loop
myfunc = mid(myfunc,1)  ' to remove leading " "

end function

Brian

 

[mr moe]

Thanks Sir God bless you and your Wife

 

[mr moe]

I tried the function but it's not working, i gives me blank results and sometimes keep looping, i have to kill the process.

 

[vbaInet]

Although Brian's suggestion should work, spikepl's suggestion of using Regex would work too but you haven't even shown us some sample records.

Are you 100% sure that there's no other string or set of numbers that are 8 characters long in your entire table? Let's see some records.

 

[mr moe]

Hi, here is an example in the attachment. Please forgive me I meant to say 10 characters long. Now I know this is not the best approach to count 10 characters because there might be other strings that don't match model ID's but I just wanted to get a start first. Can you please help?

 

[mr moe]

I mean it would serve my needs for now to just extract all strings with 10 charactrs long from column A. Please help if you can.

 

[mr moe]

vbaInet please do you have a solution

 

[Brianwarnock]

Never heard of regex until now, I've led a sheltered life!, but a quick read via links on the forum suggests that might be the way to go. On an IPad at the moment so can't try anything but if regex can pattern match
7 alphanumeric 1 numeric 2 alphanumeric it would probably find all model.ids without picking out any 10 letter words.

Love to give it a go but don' have the time at the mo.

BTW I hope that you didn't take my code as correct I assumed you knew how to do do loops and was merely indicating the functions and approach.

Brian

 

[vbaInet]

It's quite powerful Brian. You should give it a go when you've got a spare minute! Well, you will need more than a minute but hey you get the gist

I've looked at the spreadsheet but you didn't highlight what parts needs to be extracted. Can you highlight it in red or something and re-attach.

 

[mr moe]

Sorry for the delay, here is an attachment highlighted in red : thanks sir.

 

[vbaInet]

Ok, there's no consistency. In row 4, what about JP4L155197? Also in row 3 3829250 is 10 characters. So what should the criteria be?

 

[stopher]

What about CM4730FSK which is only 9 characters?

The thing is unless you can define a rule that identifies the text then you won't even be able to do it manually let alone with some code.

 

[Brianwarnock]

Assuming 10 character atleasr one of which is numeric the code below works for Excel, but I think that you have problems , it would be better if the input contained an identifier for the required string, maybe a #

Brian

Function mysrch(mystr As String) As String

Dim x As Long
Dim y As Long

myarray = Split(mystr)
x = 0
Do Until x > UBound(myarray)
    If Len(myarray(x)) <> 10 Then GoTo cont  'not 10 characters
    y = 1
    Do Until y = 11
    If IsNumeric(Mid(myarray(x), y, 1)) Then GoTo addtomysrch  ' contains a number
    y = y + 1
    Loop
addtomysrch:
    mysrch = mysrch & " " & myarray(x)
cont:
    x = x + 1
Loop

If Len(mysrch) = 0 Then
    mysrch = "NO HITS"
Else
    mysrch = Mid(mysrch, 2) 'remove leading space
    End If
    
End Function

to use
=mysrch(a1) etc