PDA

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.