Parts of a string..?

[silentwolf]

Hi guys..

So I am not sure how this can be but into a formular so I can get the wanted result on the right above in the table.
On the left side there are more numbers and Letters to the left but they are not consisted or hang on.. there are numbers like FT12345678 or TT12345678910 if the letters change..
and on the RZKTAT2KXXX they are as far as I can recall consistant with 11 charakters..and as you can see there are / slashes at different places..
Idealy I like to add at the last " " "RNR." so it Looks like last cell on the right...

Does anyone of you gurus know how to do that?

This is what I got so far but it is not correct obviously

            Case strText Like "*( 1 UMS)*"
                If strText Like "*# / ????AT????? / *" Then
                    intPos = InStrRev(strText, "/")
                    StringDelete= Mid(strText, intPos + 2)
                    
                ElseIf strText Like "*# / ????AT????? [A-Z]*#" Then
                    intPos = InStrRev(strText, " ")
                    StringDelete= Mid(strText, intPos + 2)

Many thanks for your input...

[TABLE="width: 1000"]
<tbody>[TR]
[TD]000001093566 / RZKTAT2KXXX / Our Home Furniture Greatthing 1222098[/TD]
[TD]Our Home Furniture Greatthing 1222098[/TD]
[/TR]
[TR]
[TD]000000036459 / RVVGAT2B420 Lovethe Funriture & Home GmbH Coolstuff 1352036[/TD]
[TD]Lovethe Funriture & Home GmbH Coolstuff 1352036[/TD]
[/TR]
[TR]
[TD]000074262159 / Sweetest 5295 13321[/TD]
[TD]Sweetest 5295 13321[/TD]
[/TR]
[TR]
[TD]00300035041300 / Shipping Service Centre GmbH 0634719431[/TD]
[TD]Shipping Service Centre GmbH 0634719431[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

Shipping Service Centre GmbH RNR. 0634719431

<tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
It is a bit messed up now as I took to long to post the thread so I got loged out .(
Hope it is still ok so..

I need to head of very soon so I will not be able to get back to you to soon.. but I will reply later if anyone as a suggestion

Thanks

 

[nikio8]

Here is another version.
Check first 4 words.
Delete them if they are behind /.

Function GDR(r As String) As String    
On Error Resume Next
    Dim t, i As Integer
    
    t = Split(Trim(r))
    
   
'delete first two words, if forth is /, delete 3rd and forth

t(0)=""
t(1)=""
       If t(3) = "/" Then
            t(2) = ""
            t(3)=""
        End If 
    GDR = Trim(Join(t))

 

[silentwolf]

Thanks Nikio8,
I will check it and see what I need to change..

Cheers

 

[Rick Rothstein]

it is only because the first letter is in lcase...

In that case, give this a try (it assumes the letters in the codes located before the descriptions are always upper case though)...

Function GetDescription(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 3) Like " [A-Za-z][a-z]" Then
      GetDescription = Mid(S, X + 1)
      Exit Function
    End If
  Next
End Function

 

[silentwolf]

Thanks Ricki I will try that!

 

[silentwolf]

Hi Ricki,

can you please give me one more help to the GetDescription function?
[TABLE="width: 500"]
<tbody>[TR]
[TD]) / Überweisung AT1234567891234567891 41254212512112124512
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]) / Überweisung AT1234567891234567891 / Text I need
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]41254212512112124512
[/TD]
[/TR]
[TR]
[TD]Text I need
[/TD]
[/TR]
</tbody>[/TABLE]

Here are are always AT followed by 19 Numbers but it also can be DE followed by 21 Numbers but that might not be relavant the thing is that I need either the first space after
AT or the first Letter or Number after the backslash followed by a space.

Can you change the given function to make that work as well?
Or should it be a hole new one for that case?

All others are working fine it is just those who are not working as it starts with the function at Überweisung

Would be great if you can helpe once more with that one..

Many thanks to you!!

Chees Albert

 

[Rick Rothstein]

can you please give me one more help to the GetDescription function?
[TABLE="width: 500"]
<tbody>[TR]
[TD]) / Überweisung AT1234567891234567891 41254212512112124512[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]) / Überweisung AT1234567891234567891 / Text I need[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]41254212512112124512[/TD]
[/TR]
[TR]
[TD]Text I need[/TD]
[/TR]
</tbody>[/TABLE]

Your example (examples?) is not clear to me. You have "Text I need" shown twice and it looks like they are identifying different parts. Can you show more clearly what the text is and what you want from it?

 

[silentwolf]

Hi Ricky,

Sorry the table did not display correctly..

[TABLE="width: 500"]
<tbody>[TR]
[TD]) / Überweisung AT984377000012022080 43770160104X008550012[/TD]
[/TR]
[TR]
[TD]) / Überweisung AT152070604500093184 / Some Name[/TD]
[/TR]
[TR]
[TD]This above is how it looks like now
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]43770160104X008550012[/TD]
[/TR]
[TR]
[TD]Some Name[/TD]
[/TR]
[TR]
[TD]this is what I like to have[/TD]
[/TR]
</tbody>[/TABLE]



The only difference of those is that once there is a space followed by what I need and the other is a backslash and a space followed by what I need.
Bevor the ) there is also alot of text but I guess it is not relevant as the function could start to look for AT############### but the thing is that it also can be a DE or in my
case.

Hope this is a bit clearer?
How to you enter a nice looking table like I see here at other posts? Is it only with Excel Jeanny possible? I had it bevor I think but that is long time ago .. maybe you could tell me
where to find it so I can post a bit better looking threats

it is not showing again the columns sorry for that