| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleteing a portion of a cell.
I have a problem I am not sure how to solve. Until now I have been
doing this process row by row myself. One column of my datatable contains Purchase Orders Numbers prior to the purchased item list. The PO#'s always begin with Y851and can be upwards of 20 digits beyond that (ex. Y8518945 or Y85199753532453564356 both are valid PO#'s that could arise etc.) There is always a space separating the PO# from the item list. What I am in need of is a macro that will delete the PO#'s from each row if I were to select the entire column, but that would leave the item list following the PO# untouched. Any help would be greatly appreciated. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleteing a portion of a cell.
Let me see if I understand what you are saying. So you have 1 column of text, that is basically PO number AND Item number So for example PO number is Y85136954 and Item number is 1542A23, you would see "Y85136954 1542A23" in the cell. and you want to get rid of the PO number and just leave Item number in the cell. Did I get that right? Assuming that this column is in A: =RIGHT(A1, LEN(A1)-SEARCH(" ",A1)) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=573846 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleteing a portion of a cell.
What it is is in each cell of a column (example A) there is a list,
example (Y8513654 Chair, Desk, Pencil) ... I need the Y851####### gone, and the list of items to remain the only contents of each cell in the column. Bearacade wrote: Let me see if I understand what you are saying. So you have 1 column of text, that is basically PO number AND Item number So for example PO number is Y85136954 and Item number is 1542A23, you would see "Y85136954 1542A23" in the cell. and you want to get rid of the PO number and just leave Item number in the cell. Did I get that right? Assuming that this column is in A: =RIGHT(A1, LEN(A1)-SEARCH(" ",A1)) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=573846 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleteing a portion of a cell.
Hi JokerFrowns
Try below, please. Option Explicit '---------------------------------------------------------- ' Procedure : Removexxxx ' Date : 20060821 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Remove x. Eg.Y851xxxx Chair, Desk, Pencil ' Note : Select Range befor Run Removexxxx. '---------------------------------------------------------- ' Sub Removexxxx() Dim cell As Range Dim lenbeforespace As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each cell In Selection lenbeforespace = InStr(Trim(cell.Value), " ") If lenbeforespace = 9 Then cell.Value = Left(Trim(cell.Value), 4) _ & Mid(Trim(cell.Value), lenbeforespace , Len(Trim(cell.Value))) End If Next cell Application.Calculation = xlCalculationAutomatic End Sub -- Best regards Joergen Bondesen "JokerFrowns" wrote in message ups.com... What it is is in each cell of a column (example A) there is a list, example (Y8513654 Chair, Desk, Pencil) ... I need the Y851####### gone, and the list of items to remain the only contents of each cell in the column. Bearacade wrote: Let me see if I understand what you are saying. So you have 1 column of text, that is basically PO number AND Item number So for example PO number is Y85136954 and Item number is 1542A23, you would see "Y85136954 1542A23" in the cell. and you want to get rid of the PO number and just leave Item number in the cell. Did I get that right? Assuming that this column is in A: =RIGHT(A1, LEN(A1)-SEARCH(" ",A1)) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=573846 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleteing a portion of a cell.
That's till not quite correct ... I need the Y851, and ALL following
digits removed (ex. Y815678954 Chair, Desk, Pencil ... would become a cell just containing the data Chair, Desk, Pencil) ... Joergen Bondesen wrote: Hi JokerFrowns Try below, please. Option Explicit '---------------------------------------------------------- ' Procedure : Removexxxx ' Date : 20060821 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Remove x. Eg.Y851xxxx Chair, Desk, Pencil ' Note : Select Range befor Run Removexxxx. '---------------------------------------------------------- ' Sub Removexxxx() Dim cell As Range Dim lenbeforespace As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each cell In Selection lenbeforespace = InStr(Trim(cell.Value), " ") If lenbeforespace = 9 Then cell.Value = Left(Trim(cell.Value), 4) _ & Mid(Trim(cell.Value), lenbeforespace , Len(Trim(cell.Value))) End If Next cell Application.Calculation = xlCalculationAutomatic End Sub -- Best regards Joergen Bondesen "JokerFrowns" wrote in message ups.com... What it is is in each cell of a column (example A) there is a list, example (Y8513654 Chair, Desk, Pencil) ... I need the Y851####### gone, and the list of items to remain the only contents of each cell in the column. Bearacade wrote: Let me see if I understand what you are saying. So you have 1 column of text, that is basically PO number AND Item number So for example PO number is Y85136954 and Item number is 1542A23, you would see "Y85136954 1542A23" in the cell. and you want to get rid of the PO number and just leave Item number in the cell. Did I get that right? Assuming that this column is in A: =RIGHT(A1, LEN(A1)-SEARCH(" ",A1)) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=573846 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleteing a portion of a cell.
Did you try my formula? I put this in A1: Y8513654 Chair put this in B1: =RIGHT(A1, LEN(A1)-SEARCH(" ",A1)) and got Chair I put this in A2: Y851365884 Desk put this in B2: =RIGHT(A2, LEN(A2)-SEARCH(" ",A2)) and got Desk .... -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=573846 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleteing a portion of a cell.
Hi JokerFrowns
Split Y815678954 Chair, Desk, Pencil for me, please. Cell A1: Y815678954 Chair, Desk, Pencil..... Cell ?: Y815 and cell ?: Chair, Desk, Pencil.... -- Best regards Joergen Bondesen "JokerFrowns" wrote in message ups.com... That's till not quite correct ... I need the Y851, and ALL following digits removed (ex. Y815678954 Chair, Desk, Pencil ... would become a cell just containing the data Chair, Desk, Pencil) ... Joergen Bondesen wrote: Hi JokerFrowns Try below, please. Option Explicit '---------------------------------------------------------- ' Procedure : Removexxxx ' Date : 20060821 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Remove x. Eg.Y851xxxx Chair, Desk, Pencil ' Note : Select Range befor Run Removexxxx. '---------------------------------------------------------- ' Sub Removexxxx() Dim cell As Range Dim lenbeforespace As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each cell In Selection lenbeforespace = InStr(Trim(cell.Value), " ") If lenbeforespace = 9 Then cell.Value = Left(Trim(cell.Value), 4) _ & Mid(Trim(cell.Value), lenbeforespace , Len(Trim(cell.Value))) End If Next cell Application.Calculation = xlCalculationAutomatic End Sub -- Best regards Joergen Bondesen "JokerFrowns" wrote in message ups.com... What it is is in each cell of a column (example A) there is a list, example (Y8513654 Chair, Desk, Pencil) ... I need the Y851####### gone, and the list of items to remain the only contents of each cell in the column. Bearacade wrote: Let me see if I understand what you are saying. So you have 1 column of text, that is basically PO number AND Item number So for example PO number is Y85136954 and Item number is 1542A23, you would see "Y85136954 1542A23" in the cell. and you want to get rid of the PO number and just leave Item number in the cell. Did I get that right? Assuming that this column is in A: =RIGHT(A1, LEN(A1)-SEARCH(" ",A1)) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=573846 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract portion of cell contents | Excel Worksheet Functions | |||
| vLookup a portion of a no. in a cell | Excel Worksheet Functions | |||
| Extract portion of a cell | Excel Discussion (Misc queries) | |||
| Buttons in lower portion of workbook appear in upper portion | Excel Programming | |||
| deleteing a range from one cell within it? | Excel Programming | |||
Hybrid Mode
