PDA

View Full Version : VBA to Insert a character in a specific position in a cell



simora
01-28-2021, 06:37 AM
Trying to insert a "," in the 7th position from the right, if a cell length is > 6 AND the Cell contains a " . "

Bob Phillips
01-28-2021, 07:57 AM
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
01-28-2021, 10:01 PM
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
01-28-2021, 11:25 PM
Is this what you mean Bob meant?

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

simora
01-29-2021, 06:53 AM
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
01-29-2021, 07:20 AM
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
01-29-2021, 09:12 AM
Len(cl)

snb
01-29-2021, 09:35 AM
correct.

jolivanes
01-29-2021, 10:16 AM
as usual!

snb
01-29-2021, 02:32 PM
snoever :whistle:

jolivanes
01-29-2021, 09:42 PM
As ge oe čige nie kietelt dan laŕchte nooit!:hi:

snb
01-30-2021, 04:49 AM
Kruikezeikers ?

jolivanes
01-30-2021, 08:56 AM
Parel vh zuiden aka 't kielegat

snb
01-30-2021, 09:46 AM
compris !