VBA Excel 2013 Finding specific string length and adding a value

Question

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

Cell BZ2 = 9.48E+00
My macro finds the decimal point and replaces it with 94811E-5
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

I thought that I would need to add the next step after my last if? — user3253856
– user3253856, Commented Jan 30, 2014 at 16:07

user1759942 · Accepted Answer · 2014-01-30 16:21:47Z

1

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.

edited Jan 30, 2014 at 16:21

answered Jan 30, 2014 at 16:12

user1759942

1,3466 gold badges14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1759942 Over a year ago

not fail,just add an extra 0 haha could add if len(text) = 8 also. like ...and len(text = 8) cuz really Len(Left(text, Len(text) - 3)) = 5 is fancy for length = 8

brettdj · Accepted Answer · 2014-02-01 05:14:44Z

0

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

answered Feb 1, 2014 at 5:14

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Collectives™ on Stack Overflow

VBA Excel 2013 Finding specific string length and adding a value

2 Answers 2

edit

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

edit

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related