Parts of a string..?

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,216
Office Version
  1. 2016
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 :eeek:

Code:
            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
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Code:
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
AB
1000001093566 / RZKTAT2KXXX / Our Home Furniture Greatthing 1222098Our Home Furniture Greatthing 1222098
2000000036459 / RVVGAT2B420 Lovethe Funriture & Home GmbH Coolstuff 1352036Lovethe Funriture & Home GmbH Coolstuff 1352036
3000074262159 / Sweetest 5295 13321Sweetest 5295 13321
400300035041300 / Shipping Service Centre GmbH 0634719431Shipping Service Centre GmbH 0634719431
Sheet1
Cell Formulas
RangeFormula
B1=GetDescription(A1)
B2=GetDescription(A2)
B3=GetDescription(A3)
B4=GetDescription(A4)
 
Upvote 0
Hi Scott,

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

[TABLE="width: 800"]
<tbody>[TR]
[TD]2300000029884 / Manu Fischerin 559/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50300000005710 / Tiroler Landscape 4/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]601801001791 / Hans Guter Lohn 8/2016[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Manu Fischerin 559/2015[/TD]
[/TR]
[TR]
[TD]Tiroler Landscape 4/2016[/TD]
[/TR]
[TR]
[TD]Hans Guter Lohn 8/2016[/TD]
[/TR]
</tbody>[/TABLE]


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
 
Upvote 0
Does this UDF do what you want...
Code:
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
 
Upvote 0
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


[TABLE="width: 500"]
<tbody>[TR]
[TD]easyliving Someothertext
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
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.
 
Upvote 0
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..(
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top