Read and Convert specific number range

M

misbah25

Board Regular
Joined
Aug 6, 2012
Messages
206
Dear all,

I have the following input in column E:
m87s
m60s
m78s
m80s
m30s
m45s

<tbody>
</tbody>

I would like the following output in column F:
E
F
m87s
87.5
m60s
65
m78s
78.5
m80s
85
m30s
35
m45s
45.5

<tbody>
</tbody>

I have the following code, which i would like to convert specific numbers such as M75s-->75.5 and M40s-->40.

Code: 
Sub MG08Aug32()
Dim Rng     As Range
Dim Dn      As Range
Dim n       As Integer
Dim Num     As String
Set Rng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
Columns("F:F").ClearContents
For Each Dn In Rng
    If Not Dn.Offset(, 4) = "CMBS" Then
        For n = 1 To Len(Dn)
            If IsNumeric(Mid(Dn, n, 1)) Or Mid(Dn, n, 1) = Chr(46) Then
                Num = Num & Mid(Dn, n, 1)
            End If
        Next n
    'If IsNumeric(Left(Dn, 1)) Or Len(Num) = Len(Dn) Then Dn.Offset(, 1) = Num
        'Num = ""
    If IsNumeric(Left(Dn, 1)) Or Len(Num) = Len(Dn) Then
    Dn.Offset(, 1) = Num
    
    ElseIf Num = "" Then
        Select Case LCase(Dn)
        'Teens
        Case "teens": Dn.Offset(, 1) = 16
        Case "vlteens": Dn.Offset(, 1) = 13       
        End Select
    
    Else
    Select Case UCase(Left(Dn, 1))
       Case "V"
            If UCase(Left(Dn, 2)) = "VL" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 1
            ElseIf UCase(Left(Dn, 2)) = "VH" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 9
            ElseIf UCase(Left(Dn, 4)) = "V HI" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 5, 3)) + 9
             End If
       
        Case "H"
        If UCase(Left(Dn, 2)) = "HI" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 8
            Else
                Dn.Offset(, 1) = Val(Mid(Dn, 2, 3)) + 8
         End If
        
        Case "L"
         If UCase(Left(Dn, 2)) = "LM" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 2)) + 3.5
         ElseIf UCase(Left(Dn, 3)) = "L/M" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
         ElseIf UCase(Left(Dn, 3)) = "LOW" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
        ElseIf UCase(Left(Dn, 3)) = "LO-" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
        ElseIf UCase(Left(Dn, 6)) = "LO MID" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 7, 3)) + 3.5
        ElseIf UCase(Left(Dn, 3)) = "LO " Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
        
        'ElseIf UCase(Left(Dn, 1)) = "L" Then
                'Dn.Offset(, 1) = Val(Mid(Dn, 2, 3)) + 2
                ElseIf IsNumeric(Mid(Dn, 2, 1)) Then
                Dn.Offset(, 1) = IIf(IsNumeric(Mid(Dn, 2, 1) + Mid(Dn, 3, 1)), Val(Mid(Dn, 2, 3)) + 2, Val(Mid(Dn, 2, 1)) + 0.2)
        
                'Else
                'Dn.Offset(, 1) = Val(Mid(Dn, 2, 1)) + 0.2
         End If
        
        
        Case "M"
         If UCase(Left(Dn, 2)) = "MH" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 7
        ElseIf UCase(Left(Dn, 2)) = "ML" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 3.5
        ElseIf UCase(Left(Dn, 6)) = "MTeens" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 15
        
        ElseIf UCase(Left(Dn, 3)) = "M/H" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 7 'Or Val(Mid(Dn, 4, 1)) + 0.7
                
                
        ElseIf UCase(Left(Dn, 4)) = "MID " Then
                Dn.Offset(, 1) = Val(Mid(Dn, 5, 3)) + 5
        ElseIf UCase(Left(Dn, 3)) = "MID" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 3)) + 5
        ElseIf UCase(Left(Dn, 7)) = "MID-HI " Then
                Dn.Offset(, 1) = Val(Mid(Dn, 8, 3)) + 7
        ElseIf UCase(Left(Dn, 5)) = "MID-HI" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 6, 3)) + 7
        ElseIf UCase(Left(Dn, 4)) = "MID-" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 5, 3)) + 5
         ElseIf IsNumeric(Mid(Dn, 2, 1)) Then
                Dn.Offset(, 1) = IIf(IsNumeric(Mid(Dn, 2, 1) + Mid(Dn, 3, 1)), Val(Mid(Dn, 2, 2)) + 5, Val(Mid(Dn, 2, 1)) + 0.5)
         End If
         
End Select
End If
If InStr(Dn, "-") Then
       Dn.Offset(, 1) = Split(Dn, "-")(0)
End If
Num = ""
End If
Next Dn
End Sub


Is this problem plausible and any help would be very much appreciated.

Kind regards
Misbah
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
misbah25 said:
Dear all,

I have the following input in column E:
m87s
m60s
m78s
m80s
m30s
m45s

<tbody>
</tbody>

I would like the following output in column F:
EF
m87s87.5
m60s65
m78s78.5
m80s85
m30s35
m45s45.5

<tbody>
</tbody>

I have the following code, which i would like to convert specific numbers such as M75s-->75.5 and M40s-->40.

Is this problem plausible and any help would be very much appreciated.

Kind regards
Misbah
Click to expand...
for the data you list you can try this code
Code: 
Sub change_numbers()
Dim lr As Long, j As Long, q As Single
lr = Range("E" & Rows.Count).End(xlUp).Row
For j = 1 To lr
q = Mid(Range("E" & j), 2, 2)
If Application.IsNumber(CInt(q)) Then
    If Right(q, 1) = "0" Then
        q = q + 5
    Else
        q = q + 0.5
    End If
    Range("F" & j) = q
End If
Next j
End Sub
although it's unclear to me just what the somewhat complex-looking code you list is supposed to do in this context.
 
Upvote 0
Hi,

I would like to add following key to my existing code below using the code above from post 2, if possible.


Key:
VL(x0) s+1
H(x0) s+8
VL(x0)+0.1
H(x0)+0.8
VL(xx)+0.1
H(xx)+0.8
VL(xx) s+0.1
H (xx)s+0.8

<tbody>
</tbody>
(xx) - number not a multiple of 10 (67,89)
(x0) - number multiple of 10s (30s, 50s)
To calculate the values in column E, the above key is used:

Input column E:
VL90s
H90s
VL90
H90
VL92s
H92s
VL92
H92

<tbody>
</tbody>

Output column F:
EF
VL90s91
H90s98
VL9097.1
H9091.8
VL92s92.1
H92s92.8
VL9292.1
H9292.8

<tbody>
</tbody>

The above tables show sample data, as I have many different string variables (seen from code below “LM60s, VH30s etc).

I would like to combine my code (below) with code above in post 2, if possible otherwise any help would be appreciates, so if it’s VL72s it uses the above key else if it is VL70s it uses my code function below.

Code: 
Sub MG08Aug32()
Dim Rng     As Range
Dim Dn      As Range
Dim n       As Integer
Dim Num     As String
Set Rng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
Columns("F:F").ClearContents
For Each Dn In Rng
[B][COLOR=#b22222]'[/COLOR][COLOR=#008080]DO not run this function on rows which equal to CMBS.[/COLOR][/B]
    If Not Dn.Offset(, 4) = "CMBS" Then
        [COLOR=#008080][B]'if the values are 45a or 76h then extract numbers only.[/B][/COLOR]
        For n = 1 To Len(Dn)
            If IsNumeric(Mid(Dn, n, 1)) Or Mid(Dn, n, 1) = Chr(46) Then
                Num = Num & Mid(Dn, n, 1)
            End If
        Next n
    'If IsNumeric(Left(Dn, 1)) Or Len(Num) = Len(Dn) Then Dn.Offset(, 1) = Num
        'Num = ""
    If IsNumeric(Left(Dn, 1)) Or Len(Num) = Len(Dn) Then
    Dn.Offset(, 1) = Num
    
    ElseIf Num = "" Then
        Select Case LCase(Dn)
       [B][COLOR=#008080] 'Teens[/COLOR][/B]
        Case "teens": Dn.Offset(, 1) = 16
        Case "vlteens": Dn.Offset(, 1) = 13
              End Select
    
    Else
    Select Case UCase(Left(Dn, 1))
       [B][COLOR=#008080]'Calculate VH multiples of 10s.[/COLOR][/B]
       Case "V"
            If UCase(Left(Dn, 2)) = "VL" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 1
            ElseIf UCase(Left(Dn, 2)) = "VH" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 9
            ElseIf UCase(Left(Dn, 4)) = "V HI" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 5, 3)) + 9
             End If
       
        Case "H"
      [COLOR=#008080][B]  'Calculate H multiple of 10s (H60s)[/B][/COLOR]
        If UCase(Left(Dn, 2)) = "HI" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 8
            Else
                Dn.Offset(, 1) = Val(Mid(Dn, 2, 3)) + 8
         End If
        
        Case "L"
         If UCase(Left(Dn, 2)) = "LM" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 2)) + 3.5
         ElseIf UCase(Left(Dn, 3)) = "L/M" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
         ElseIf UCase(Left(Dn, 3)) = "LOW" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
        ElseIf UCase(Left(Dn, 3)) = "LO-" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
        ElseIf UCase(Left(Dn, 6)) = "LO MID" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 7, 3)) + 3.5
        ElseIf UCase(Left(Dn, 3)) = "LO " Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
        
        'ElseIf UCase(Left(Dn, 1)) = "L" Then
                'Dn.Offset(, 1) = Val(Mid(Dn, 2, 3)) + 2
                ElseIf IsNumeric(Mid(Dn, 2, 1)) Then
                Dn.Offset(, 1) = IIf(IsNumeric(Mid(Dn, 2, 1) + Mid(Dn, 3, 1)), Val(Mid(Dn, 2, 3)) + 2, Val(Mid(Dn, 2, 1)) + 0.2)
        
                'Else
                'Dn.Offset(, 1) = Val(Mid(Dn, 2, 1)) + 0.2
         End If
        
        
        Case "M"
         If UCase(Left(Dn, 2)) = "MH" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 7
        ElseIf UCase(Left(Dn, 2)) = "ML" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 3.5
        ElseIf UCase(Left(Dn, 6)) = "MTeens" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 15
        
        ElseIf UCase(Left(Dn, 3)) = "M/H" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 7 'Or Val(Mid(Dn, 4, 1)) + 0.7
                
                
        ElseIf UCase(Left(Dn, 4)) = "MID " Then
                Dn.Offset(, 1) = Val(Mid(Dn, 5, 3)) + 5
        ElseIf UCase(Left(Dn, 3)) = "MID" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 4, 3)) + 5
        ElseIf UCase(Left(Dn, 7)) = "MID-HI " Then
                Dn.Offset(, 1) = Val(Mid(Dn, 8, 3)) + 7
        ElseIf UCase(Left(Dn, 5)) = "MID-HI" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 6, 3)) + 7
        ElseIf UCase(Left(Dn, 4)) = "MID-" Then
                Dn.Offset(, 1) = Val(Mid(Dn, 5, 3)) + 5
         ElseIf IsNumeric(Mid(Dn, 2, 1)) Then
                Dn.Offset(, 1) = IIf(IsNumeric(Mid(Dn, 2, 1) + Mid(Dn, 3, 1)), Val(Mid(Dn, 2, 2)) + 5, Val(Mid(Dn, 2, 1)) + 0.5)
         End If
         
End Select
End If
If InStr(Dn, "-") Then
       Dn.Offset(, 1) = Split(Dn, "-")(0)
End If
Num = ""
End If
Next Dn
End Sub

Thank you so much for your time and help.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

montecarlo2012
  • Solved
vba=Loop through each row in the data range.(B2:F10000)
Replies
9
Views
2K
montecarlo2012
montecarlo2012
S
  • Question
Change date format inside textbox userform
Replies
1
Views
146
Akuini
Akuini
TAPS_MikeDion
  • Question
Help needed with making a faster, more efficient VBA code
2 3
Replies
21
Views
1K
TAPS_MikeDion
TAPS_MikeDion
rjplante
  • Solved
sort numbers with letters
2 3
Replies
24
Views
614
Akuini
Akuini
montecarlo2012
  • Solved
Read sheet 3 results on sheet 1
Replies
4
Views
522
montecarlo2012
montecarlo2012

Forum statistics

Threads
1,216,545
Messages
6,131,282
Members
449,641
Latest member
paulabrink78

Share this page

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