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...

000001093566 / RZKTAT2KXXX / Our Home Furniture Greatthing 1222098	Our Home Furniture Greatthing 1222098
000000036459 / RVVGAT2B420 Lovethe Funriture & Home GmbH Coolstuff 1352036	Lovethe Funriture & Home GmbH Coolstuff 1352036
000074262159 / Sweetest 5295 13321	Sweetest 5295 13321
00300035041300 / Shipping Service Centre GmbH 0634719431	Shipping Service Centre GmbH 0634719431
	Shipping Service Centre GmbH RNR. 0634719431 <tbody> </tbody>

<tbody>
</tbody>

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

 

[Scott Huish]

Function GetDescription(r As String) As String
On Error Resume Next
Dim t, u
t = Split(r, "/")
t = Trim(t(UBound(t)))
u = Split(t)
If u(0) Like "????AT?????" Then u(0) = ""
GetDescription = Trim(Join(u))
End Function

Excel 2010
	A	B
1	000001093566 / RZKTAT2KXXX / Our Home Furniture Greatthing 1222098	Our Home Furniture Greatthing 1222098
2	000000036459 / RVVGAT2B420 Lovethe Funriture & Home GmbH Coolstuff 1352036	Lovethe Funriture & Home GmbH Coolstuff 1352036
3	000074262159 / Sweetest 5295 13321	Sweetest 5295 13321
4	00300035041300 / Shipping Service Centre GmbH 0634719431	Shipping Service Centre GmbH 0634719431
Sheet1

Cell Formulas
Range	Formula
B1	=GetDescription(A1)
B2	=GetDescription(A2)
B3	=GetDescription(A3)
B4	=GetDescription(A4)

 

[silentwolf]

Hi Scott,
I thank you very much for this UDF... works great!

Cheers

Albert

 

[silentwolf]

Hi Scott,

just wondering if you could make one adjustment to the function..?

2300000029884 / Manu Fischerin 559/2015
50300000005710 / Tiroler Landscape 4/2016
601801001791 / Hans Guter Lohn 8/2016

<tbody>
</tbody>

Manu Fischerin 559/2015

Tiroler Landscape 4/2016

Hans Guter Lohn 8/2016

<tbody>
</tbody>

so with your function I get the last four digits.. wondering if it could be adjusted so it takes that backslash "/" into account.
The rest is the same as with the last post.

Many thanks..

Albert

 

[Rick Rothstein]

Does this UDF do what you want...

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

 

[silentwolf]

Hi Rick,
Many thanks yes you Code is spot on .) Fantastic!!
Very Happy

 

[silentwolf]

Rick,

just a question. On some of the values I get a wrong data type error.

I have debuged it but can not make it out where the issue could be .(

It is a pretty long text so I have tried to delete parts of it but still get the error.
So how could it be changed so if there is a error use the text and I take care of those afterwards...

Because there are so many different text in the form that I believe all want work at the same time..
This happens when the text starts like below

easyliving Someothertext

<tbody>
</tbody>

Thanks..

Also as mentioned above if there is a issue or is blank after the function has processed the text keep the original text.

Kind regards
Albert

 

[Rick Rothstein]

Also as mentioned above if there is a issue or is blank after the function has processed the text keep the original text.

It would be better if you could show me one (or more) of your values that don't work so I can see how to trap it properly in order to do what you asked above.

 

[silentwolf]

Hi Rick,

it is only because the first letter is in lcase...
I tried to create a function to change first letter to ucase but no sucsess... drives me nuts ...

like to check if the first letter is lcase then change it to ucase...
else do nothing

if lcase(left(strText,1)) then

that is what I tried it does not work..(

 

[nikio8]

His function is looking for the first lower case, Like "[a-z]", and returns everything after, -1 space
That is how you sample data aperas