# 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
```

using this conditional

``` if isNumeric(left(text,5)) AND right(text,3) = "E-5" then
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
```