Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows With Non-Needed Data between Needed Data | Excel Worksheet Functions | |||
vb needed i think... | Excel Programming | |||
MVP HELP NEEDED ! | Excel Worksheet Functions | |||
HELP NEEDED... | Excel Programming | |||
Help needed! | Excel Worksheet Functions |