VBA to Insert a character in a specific position in a cell

[simora]

Trying to insert a "," in the 7th position from the right, if a cell length is > 6 AND the Cell contains a " . "

[Bob Phillips]

Off the top


If Len(cell).Value) > 6 And Instr(cell,".") > 0 Then

cell.Value = Left$(cell.Value, 6) & "," & Mid$(cell.Value, 7,255)
End If

[simora]

Thanks Bob Phillips :
(http://www.vbaexpress.com/forum/member.php?2139-Bob-Phillips)
But This is putting the "," 7 places from the Left, not the 7th position from the Right.

[jolivanes]

Is this what you mean Bob meant?

= Left$(c.Value, Len(c) - 6) & "," & Mid$(c, Len(c) - 5, 6)

[simora]

Thanks jolivanes;

This solution is what worked.



If Len(cell.Value) > 6 And InStr(cell, ".") > 0 Then
cell.Value = Left$(cell.Value, Len(cell) - 6) & "," & Mid$(cell, Len(cell) - 5, 6)
End If


Thanks for the efforts of all who contributed.

[snb]

Sufficient:


If (Len(cl) > 6)*InStr(cl, ".") Then cl = Left(cl, Len(cl) - 6) & "," & Mid(cl, Len(cl) - 5)

Avoid variable names that can interfere with VBA names.

[jolivanes]

Len(cl)

[snb]

correct.

[jolivanes]

as usual!

[snb]

snoever :whistle:

[jolivanes]

As ge oe èige nie kietelt dan laàchte nooit!:hi:

[snb]

Kruikezeikers ?

[jolivanes]

Parel vh zuiden aka 't kielegat

[snb]

compris !