Parts of a string..?

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,177
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 :eek:

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

000001093566 / RZKTAT2KXXX / Our Home Furniture Greatthing 1222098Our Home Furniture Greatthing 1222098
000000036459 / RVVGAT2B420 Lovethe Funriture & Home GmbH Coolstuff 1352036Lovethe Funriture & Home GmbH Coolstuff 1352036
000074262159 / Sweetest 5295 13321Sweetest 5295 13321
00300035041300 / Shipping Service Centre GmbH 0634719431Shipping 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
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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)
 

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,177
Office Version
  1. 2016
Hi Scott,
I thank you very much for this UDF... works great!

Cheers

Albert
 

silentwolf

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
 

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,177
Office Version
  1. 2016
Hi Rick,
Many thanks yes you Code is spot on .) Fantastic!!
Very Happy :)
 

silentwolf

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

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

Board Regular
Joined
Oct 20, 2017
Messages
128
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
 

Forum statistics

Threads
1,171,195
Messages
5,874,278
Members
433,042
Latest member
mcm2022

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
Top