Delete 4 to 5 digits from left in a cell

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

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


"saziz" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I got a sheet where I need to delete number followed by ':'. All these
> numbers are at the left in a cell like this---> 300: text.... few
> hundred rows. Any help please?
> Aziz
>
>
> --
> saziz
> ------------------------------------------------------------------------
> saziz's Profile:
> http://www.excelforum.com/member.php...fo&userid=6350
> View this thread: http://www.excelforum.com/showthread...hreadid=536939
>

With data in A1, in B1 put


=TRIM(MID(A1,FIND(":",A1)+1,255))

copy down

"saziz" wrote:

>
> Hi,
> I got a sheet where I need to delete number followed by ':'. All these
> numbers are at the left in a cell like this---> 300: text.... few
> hundred rows. Any help please?
> Aziz
>
>
> --
> saziz
> ------------------------------------------------------------------------
> saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
> View this thread: http://www.excelforum.com/showthread...hreadid=536939
>
>

Peo Sjoblom,
Thank you! It worked perfectly.
Regards

Aziz

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

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)

"saziz" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I got a sheet where I need to delete number followed by ':'. All these
> numbers are at the left in a cell like this---> 300: text.... few
> hundred rows. Any help please?
> Aziz
>
>
> --
> saziz
> ------------------------------------------------------------------------
> saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
> View this thread: http://www.excelforum.com/showthread...hreadid=536939
>

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 outside procedure"
I don't know what is.
Aziz

=TRIM(MID(Sheet2!A1,FIND(":",Sheet2!A1)+1,255))

"saziz" wrote:

>
> 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
>
>
> --
> saziz
> ------------------------------------------------------------------------
> saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
> View this thread: http://www.excelforum.com/showthread...hreadid=536939
>
>

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)

"saziz" <[email protected]> wrote in
message news:[email protected]...
>
> 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 outside
> procedure"
> I don't know what is.
> Aziz
>
>
> --
> saziz
> ------------------------------------------------------------------------
> saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
> View this thread: http://www.excelforum.com/showthread...hreadid=536939
>