How to interpretate a text string

[vicenflor]

I have a text string which usually starts with the letter M, followed by 5
chiffres and then again 1 or 2 characters : example M51235W2. The 7th
character (in this case W) is giving me info about the magnification

What I want is the following :

1) If the first character is the letter M, then the 7th character should be
checked. In case this 7th character is the letter A then the variable
"magnification" should be "1x", in case this letteris B then the
magnification should be "2x" , in case it is W then the "magnification"
should be "23x" and so on.....

2) If the first character is the letter M, then the text string should be
cut after the 6th character so that only M51235 is displayed.

Any help is kindly appreciated!

 

[Helmut Weber]

Hi,
if

Any help is kindly appreciated!

then
how about this one:

Public Function magnification(ByRef s As String) As Long
' ByRef !
If Len(s) > 8 Or Len(s) < 7 Then
magnification = 0
Exit Function
End If
If Not Left(s, 6) Like "M#####" Then
magnification = 0
Exit Function
End If
If Asc(Mid(s, 7, 1)) < 64 Or Asc(Mid(s, 7, 1)) > 90 Then
magnification = 0
Exit Function
End If
magnification = Asc(Mid(s, 7, 1)) - 64
s = Left(s, 6)
End Function
' ------------
Sub test123()
Dim s As String
s = "M12345C2"
MsgBox magnification(s)
MsgBox s
End Sub

Explanation follows if required.

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Jezebel]

If left$(pString,1) = "M" then

pMagnification = cstr$(Asc(mid$(pString, 7,1))-64) & "x"
pString = Left$(pString,6)

End if

 

[Greg Maxey]

vicneflor,

Something like the following might get you started. It seems that there
should be an easier way of defining the array from A-Z, but I don't know
how. If anyone else can help please do:

Sub ScratchMacro()
Dim magVar As String
Dim myRange As Range
Dim myArray
Dim i As Integer

'Type your example M51235W2 in the document and select it
Set myRange = Selection.Range
myArray = Array("A", "B", "C", "D", "E", "F", "G", _
"H", "I", "J", "K", "L", "M", "N", "O", _
"P", "Q", "R", "S", "T", "U", "V", "W", _
"X", "Y", "Z")
If InStr(myRange.Text, "M") = 1 Then
For i = LBound(myArray) To UBound(myArray) Step 1
If InStr(myRange.Text, myArray(i)) = 7 Then
magVar = i + 1 & "x Magnification"
End If
Next
myRange = Left(myRange, 6)
MsgBox magVar
End If
End Sub

 

[Helmut Weber]

Hi Submariner, hi Jezebel,

seems we are time synchronized,
all answers sent at 2:01 central european time,
which means it is 2 hours after midnight here.

What time is it where you are?
Though this kind of information might not help vincenflor too much.

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Jezebel]

12:01 PM for me.

 

[Greg Maxey]

The flash occurred here at 20:01 5 March.

 

[vicenflor]

Hi Helmut, Jezebel and Greg

Thanks for the information. The code which all of you returned was very
usefull for my application.

However, I have an additional question : Is it possible to transfer the text
string into capital letters (just in case the 7th letter is a small letter)?

Greetings from Belgium

 

[Jezebel]

UCase$()

Hi Helmut, Jezebel and Greg

Thanks for the information. The code which all of you returned was very
usefull for my application.

However, I have an additional question : Is it possible to transfer the
text
string into capital letters (just in case the 7th letter is a small
letter)?

Greetings from Belgium

 

[Greg Maxey]

Like

UCase$(myRange.Text)
or
UCase$(pString)