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.
- 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
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)
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