String, substring

[petergr]

Hi, I am new to Excel VBA. Please help me with this:
How can I take a part from a string and check if it is letters o
numbers. In my particular case I need to split sea containers numbers
for example HDMU1254324, so the task is to take for first letters
check if the all first letters are really letters and put the prefix o
container in one sell and the number into another cell. Thank you

 

[Guest]

You can use the ISNUMBER() worksheet function ie.

If application.WorksheetFunction.IsNumber(LEFT(stContainer,4)) = True then
......

Duncan
http://www.excel.duncan-fiona.co.uk

 

[Bob Phillips]

Here is an example

Dim s As String
Dim i As Long
Dim nChar As Long


s = "HDMU1234"

For i = 1 To 4
nChar = Asc(Mid(s, i, 1))
If nChar < Asc("A") Or nChar > Asc("Z") Then
MsgBox "Illegal character " & Mid(s, i, 1)
Exit For
End If
Next

If Not IsNumeric(Mid(s, 5, 99)) Then
MsgBox "Wrong tail " & Mid(s, 5, 99)
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[petergr]

Thank you Duncan, thank you Bob.....

 

[Dave Peterson]

I think it would be better to use VBA's isnumber() in this case.

application.isnumber will return false when used with the Left function (left
returns a string).