VBA Excel 2013 Finding specific string length and adding a value

I am trying to write a macro to find if a cell has 5 numeric values and if it does, I need to add a 0 at the end.

My macro already has some steps in it.

For example

  1. Cell BZ2 = 9.48E+00
  2. My macro finds the decimal point and replaces it with 94811E-5
  3. I need to add a Zero in this case, because there are 5 numeric values, AND only when the last three characters are E-5.

Expected result is 948110E-5.

I am using a number stored as text.

Can anyone help me out?

Sub TextFormat()

    Dim c As Range
    Dim d As Range

    For Each c In Sheets("order_export").Range("F2:F10000").Cells
        If StrComp(Right(c.Value, 1), "R", vbTextCompare) = 0 Then
            c.Offset(0, -1).Value = c.Offset(0, -1).Value & "R"
            c.Value = Left(c.Value, Len(c.Value) - 1)
        End If
    Next c

    For Each d In Sheets("order_export").Range("BZ2:BZ10000").Cells
        If InStr(1, d.Value, ".", vbTextCompare) > 0 Then
            d.NumberFormat = "@"
            d.Value = Replace(d.Value, ".", "")
            d.Value = d.Value & "E-5"
        End If
    Next d
End Sub

Answers


using this conditional

 if isNumeric(left(text,5)) AND right(text,3) = "E-5" then
     'add zero
     text = left(text,5) & "0" & right(text, len(text) - 5)
 end if

will add the 0 after the first 5 if the first 5 characters are numeric and the last 3 are e-5. the left function takes the first 5 characters. the isNumeric checks if they are numeric. and then the rest, takes the first 5 characters, puts a 0, and then the right takes all characters starting from the right going up till length - 5 (we already have the first 5 characters)

edit

as pointed out, if there is already a 0, like 123450E-5 then an extra would be added.

add ANd len(text) = 8 so that it only adds the 0 if there are 8 characters.


Excel doesn't short circuit so for coding efficiency it is better to break an AND into IF's with the most likely errors first, then the breaches

Also never using the variant functions Left and Right use string functions Left$ and Right$ instead

This link is an excellent resource re coding optimisation.

Re-cutting the earlier answers would be something like this:

c = "94811E-5"

If Len(c) = 8 Then
    If IsNumeric(Left$(c, 5)) Then
            If Right$(c, 3) = "E-5" Then c = Left$(c, Len(c) - 3) & "0" & Right$(c, 3)
    End If
End If

MsgBox c

Need Your Help

Convert factor type time into number of minutes

r time

There is a column in my dataset that contains time in the format 00:20:10. I have two questions. First, when I import it into R using read.xlsx2(), this column is converted to factor type. How can I

Firefox video tag getVideoPlaybackQuality() is not a function

javascript firefox html5-video media-source

According to MDN documentation, getVideoPlaybackQuality() on HTMLVideoElement is available after version 25.0.