How do I split String

[hill0093]

How do I code

Dim stg As String 
Dim lgt As Integer 
Dim num As Integer
lgt = Length(stg)
If First Character of stg is 'p' Then num = last (lgt-1) Characters of stg

I don't know how to get Record New Macro to tell me.

 

[patel45]

your goal is not clear for me

 

[hill0093]

In Excel 2003 I have a String.
If the first character equals p then I want to
take the rest of the string as an integer or long

 

[Jonmo1]

Try

Dim stg As String
Dim lgt As Integer
Dim num As Long
If UCase(Left(stg, 1)) = "P" Then num = CLng(Right(stg, lgt - 1))

 

[hill0093]

How do I get the length of the string?
I need it, I suppose.

 

[Jonmo1]

Sorry, I thought you already had it...

This
lgt = Length(stg)
should be changed to
lgt = Len(stg)

 

[hill0093]

Compiles and runs, probably works for me.
Thanks much.

 

[Jonmo1]

Glad to help, thanks for the feedback.

Note, the use of Ucase was to make the code NON Case Sensitive (it will work with p or P)
If you WANT it to be case sensitive (work with P, but NOT with p) then just remove the Ucase function.

 

[hill0093]

Thanks for the more-complete.

 

[Peter_SSs]

I know this is a bit old & resolved but I just stumbled across it so I'm adding a few comments.

Unless you need the length of the string stg for other purpose anyway, you don't need to calculate that.

I always use Long (not Integer) as Integer values have to be converted to Long anyway for vba to work with them (Long is shorter to write too )

Since num is declared as Long (or Integer) there should be no need to force conversion with CLng()

It is also possible to avoid extracting Left(stg,1)

I'm not suggesting anything wrong with the existing code, but here is another way to attack the problem.
It has the same case-sensitivity issues as described by Jonmo1.

Dim stg As String
Dim num As Long


If UCase(stg) Like "P*" Then num = Mid(stg, 2)