Vba help needed.

[XR8 Sprintless]

This is probably simple but my brain is on strike today.

I have some data in column a in the format
yyyxxx
xxxyyy:123456abc
aaabbb:112345yku
aaccdd


What I would like to do is for each cell that has a : in it copy the
data to the right of the : to column b and delete the : in column a.

It should be simple...

I have just done a stocktake using a bar code reader and some of the
barcodes have the serial number separated from the item code by the :.
Where parts do not have a serial number there is no :.

 

[isabelle]

hi,

when the copy is made to column b, do you want to delete the : only, or delete
the : and what is on right off :

isabelle

Le 2014-07-06 22:05, XR8 Sprintless a écrit :

 

[isabelle]

With Selection
.TextToColumns Destination:=Range(.Cells(1).Address), OtherChar:=":",
FieldInfo:=Array(Array(1, 1))
End With

isabelle

Le 2014-07-06 22:15, isabelle a écrit :

 

[isabelle]

otherwise,

For Each c In Selection
If Not IsError(Application.Find(":", c)) Then
Cells(c.Row, 2) = Right(c, Len(c) - Application.Find(":", c))
Cells(c.Row, 1) = Application.Substitute(c, ":", " ")
End If
Next

isabelle

Le 2014-07-06 22:39, isabelle a écrit :

 

[Gord Dibben]

Text to Columns will do that in a flash.

Gord

 

[XR8 Sprintless]

Text to Columns will do that in a flash.

Thanks Gord

That's how I ended up doing it. I knew it would be simple but was brain
fried that day...