View Full Version : How to truncate '.' only from the beginning of a string in MS Excel
anish.vatsya
Mar 3, 2010, 02:48 AM
I am generating an output in MS Excel . Only in one column I want to truncate '.' from beginning. If it is not starting with then leave it.
Example :
Input
Col 1
.06R215.23
.02145.HS
12HJ458
12.232.32
Output required
Col 1
06R215.23
02145.HS
12HJ458
12.232.32
KISS
Mar 3, 2010, 04:08 AM
=if(left(c1,1)=".", mid(c1,2,len(c1)-1),c1)
anish.vatsya
Mar 3, 2010, 05:59 AM
How to apply this?? Please help
KISS
Mar 3, 2010, 06:22 AM
It's not a macro and it won't change how an item is displayed, but will create a new cell of the correct form. The cell must be text. If you had an entry of .01, then enter it as '.01 which will force it to be TEXT.
JBeaucaire
Mar 3, 2010, 11:21 AM
This macro will do the same thing on the original data. It operates on the values in column B only.
Sub TruncateItemIDs()
'JBeaucaire 3/3/2010
Dim Cell As Range
For Each Cell In Range("B:B").SpecialCells(xlCellTypeConstants)
If Left(Cell, 1) = "." Then Cell = Mid(Cell, 2, 99)
Next Cell
End Sub
=======
How/Where to install the macro:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet
The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.