Display all untill a special character is found. (1 Viewer)

[AccessVBANewbie]

First of all apologies for restarting this post as the previous one I did not explain myself properly. I hope I have done a better job now. I would appreciate anyone who could spare their time to have a look at this issue I am having. Please let me know if you need any further clarifications. Thank you very much.

L=Letter , X=number. Examples consisting of three parts (it can be more than three).

1. LLLLXXXXXX_LLLLXXXXXX_LLLLXXXXXX
OR
2. LLLLXXXXXX LLLLXXXXXX LLLLXXXXXX
OR
3. LLLLXXXXXX&LLLLXXXXXX&LLLLXXXXXX
OR
4. LLLL_LLLLXXXXXX&LLLLXXXXXX
OR
5. LL LLLLXXXXXX&LLLLXXXXXX

I would like to develop a function that takes a string that is made up of three parts or more and each part is separated by an underscore “_”, space, or “&”. There is no character that takes precedence over the other. It will always be only a single character which seperates the parts.

The check will always be done from the left. It should carry out the following actions:

1. Display all characters from the left until it reaches the first seperator “_” underscore OR “space” or “&”

2. Check only Part1 if it is made up of 4 letters and the rest 6 numbers (see 1-3 above). If that is the case only return part 1.

3. If part one only has letters and no numbers (see above 4-5) return all parts. Part one can consist of two letters or more.


I have started the function for step 1 above that searches for the character “_” underscore and display all characters until the first “_” is reached. I would like to include the same for “Space” or “&” in this function.

Once that is done I would like to do step 2 and step 3.

[COLOR=black][FONT=Verdana][SIZE=3][FONT=Calibri]Function SearchforChar(strTest As String) As String[/FONT][/SIZE][/FONT][/COLOR]
[FONT=Verdana][COLOR=black][SIZE=3][FONT=Calibri]Dim test2 As String[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Calibri]Dim strUntil As String[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Calibri]strUntil = "_" [/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Calibri]test2 = Left(strTest, InStr(1, strTest, strUntil) - 1)[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Calibri]SearchforChar = test2[/FONT][/SIZE][/COLOR]
[COLOR=black][FONT=Calibri][SIZE=3]End Function[/SIZE][/FONT][/COLOR]
[/FONT]

Situation 1:
LLLLXXXXXX_LLLLXXXXXX_LLLLXXXXXX
Answer1: LLLLXXXXXX

Situation 2:
LLLLXXXXXX LLLLXXXXXX&LLLLXXXXXX
Answer2: LLLLXXXXXX

Situation 3:
LLLLLLLLLLLLL_LLLLXXXXXX&LLLLXXXXXX
Or
LL&LLLLXXXXXX_LLLLXXXXXX
OR
LL LLLLXXXXXX LLLLXXXXXX
OR
LLLL

Answer3:
Full name with all parts should be returned for all above four cases under situation 3.

 

[CJ_London]

Try this - I'm sure others can make it more efficient but it should do what you require

Function SearchforChar(strTest As String) As String
'returns a zero length string ("") if does not meet the required pattern
Dim strTmp As String
Dim i as integer
 
    SearchForChar=""
    strTmp=""
    if instr(strTest,"&")=11 then  'has ampersand in the right place
        strTmp=left(strTest,10)
    else
        if instr(strTest," ") =11 then 'has space in the right place
                 strTmp=left(strTest,10)
         else
              if instr(strTest,"_") =11 then 'has underscore in the right place
                  strTmp=left(strTest,10)
              end if
         end if
    end if
    if len(strTmp)=10 then 'a value has been found, now check for 4 chars & 6 numbers
        for i=1 to 10
            if i<5 if instr("1234567890",mid(strTmp,i,1))<>0 then 'char is not alpha
                  exit function
            elseif instr("1234567890",mid(strTmp,i,1))=0 then 'char is not numeric
                  exit function
            end if
        next i
        SearchForChar=strTmp
     end if
 
End Function

 

[AccessVBANewbie]

Thank you will give it a test run.

 

[pr2-eugin]

Did you delete the other thread, where I was helping you?

You just wasted some time for me there ! Good Luck with whatever you are upto !

 

[AccessVBANewbie]

Sorry Paul, I hope you are not upset.

I deleted it and started a newone beause I thought no one was understanding me with your help I have re-written problem in a more clear manner.

 

[AccessVBANewbie]

Hi

is it possible to include 2 "&&" with the following combination:

If InStr(strProjectCode, "&") = 11 Then
   SubString = Left(strProjectCode, 10)
 
[B]ElseIf InStr(strProjectCode, "&&") = 11 Then[/B]
[B]   SubString = Left(strProjectCode, 10)[/B]
[B]end if[/B]

I was wondering if I am able to make InStr see 2 charachers as well.

 

[CJ_London]

yes - but you don't need to change the code since the

If InStr(strProjectCode, "&") = 11

will find the first '&' at position 11 and you are only interested in the first 10 characters

 

[AccessVBANewbie]

Hi

that is true.

How about if I want it to search in part 2 instead of 1 and look for "_&" at the same time.

LLLLXXXXXX_&LLLLXXXXXX_LLLLXXXXXX

Thanks

 

[CJ_London]

then you need to use substantially different code -

Investigate the use of the split function, you would have something like this to replace the each if part of the code in the first group of if statements

dim S() as string
 
s=split(srtTest,"&")
for i=0 to ubound(s)
    if len(s(i))=10 then substr=s(i)
next i

This will look at all 3 parts of your original string with the requirement that the 'divider' is "&" (or " " or "_" as specified in the split function) and you do not have a mix of & and _ for example in strTest

 

[AccessVBANewbie]

Thank you much appriciated.