Vba help needed.
 

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 :.

Vba help needed.
 

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 :
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 :.

Vba help needed.
 

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 :
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 :
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 :.

Vba help needed.
 

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 :

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 :
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 :
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 :.

Vba help needed.
 

Text to Columns will do that in a flash.

Gord

On Mon, 07 Jul 2014 12:05:11 +1000, XR8 Sprintless
wrote:

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 :.

Vba help needed.
 

On 8/07/2014 5:29 AM, Gord Dibben wrote:
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...