Delete 4 to 5 digits from left in a cell

S

saziz

Hi,
I got a sheet where I need to delete number followed by ':'. All thes
numbers are at the left in a cell like this---> 300: text.... fe
hundred rows. Any help please?
Azi
 
P

Peo Sjoblom

If : only occurs once per cell, select the column, do data>text to columns,
select delimited and click next, select other and put : in the other box,
click next, select the left column in the data preview (should be black)
which is the default, then select do not import (skip) and click finish

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
B

Bob Phillips

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 To iLastRow
With Cells(i,"A")
iPos = InStr(":",.Value)
if iPos > 0 Then
.Value = Right(.Value, Len(.Value) - iPos)
End If
End With
Next i

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
S

saziz

Toppers,
That worked too. Now instead on the same sheet I am trying this on
sheet2 giving this code. =TRIM(MID(Sheet1!B2,FIND(":",B2)+1,255)) It
does not work. I don't know why. Any idea?
Thanks
Aziz
 
S

saziz

Hi Bob,
I fix my problem, thank you. But wanted to try your code.
At this line: For i = 1 To Len(a) it gives me an error "Invalid outsid
procedure"
I don't know what is.
Azi
 
B

Bob Phillips

You need to put it in a macro, for instance

Sub RemoveData()
iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 To iLastRow
With Cells(i,"A")
iPos = InStr(":",.Value)
if iPos > 0 Then
.Value = Right(.Value, Len(.Value) - iPos)
End If
End With
Next i
End Sub

then run RemoveData

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top