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

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

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

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

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

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

Bearacade, there is data in every cell, columns A through K, Column L
is the one containing the data which I need filtered (PO#'s Removed)
.... The other Columns need to remain as they are, while the PO#'s get
removed from the entries in Column L.

I am sorry if I am not explaining it too well, or if what others has
identified will perform the operation, I am not very excel savy.

Thank you very much for all the effort so far. Think this is doable?

Bearacade wrote:
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

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

There is 2 things you can do, you can either write a macro that will
allow you to filter it out. But if you are unfamiliar with Excel, I
won't go around messing with Macros...

Or you can Insert 2 columns after L (right click on column M, and
select insert twice)

Now you have 2 new empty column between L and O (O used to be M)

Now, in the next column M, insert my formula and drag down, this will
cut the data as you want them, with the PO number striped

Select Column M and Copy

Select Column N and Paste Special, Values

Now Delete Column L and M

You will have 1 single column (L) with values that has the striped POs.


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=573846

Hi JokerFrowns

I think I misunderstood you question.
I have change the macro.
Feel free to try it.


Option Explicit

'----------------------------------------------------------
' Procedure : RemoveLeftBeforeComma
' Date : 20060821a
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Remove everything before 1th space.
' Eg. 'Y851598 Chair, Desk, Pencil' =
' 'Chair, Desk, Pencil'
' Space must come before comma.
' Note : Select Range befor Run Removexxxx.
'----------------------------------------------------------
'
Sub RemoveLeftBeforeComma()
Dim cell As Range
Dim lenbeforespace As Long
Dim lenbeforecomma As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


For Each cell In Selection
lenbeforespace = InStr(Trim(cell.Value), " ")
lenbeforecomma = InStr(Trim(cell.Value), ",")

If lenbeforecomma lenbeforespace Then
cell.Value = Mid(Trim(cell.Value), _
lenbeforespace + 1, Len(Trim(cell.Value)))
End If
Next cell

Application.Calculation = xlCalculationAutomatic
End Sub


--
Best Regards
Joergen Bondesen


"Joergen Bondesen" wrote in message
...
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

A macro something like this should work, providing there are no empt
cells or cells containing other data in the column (is there a heade
row?)

Code
-------------------
Sub poremove(

Application.ScreenUpdating = Fals

' Insert 1 temp 'Helper' colum

Columns("M:M").Selec
Selection.Insert shift:=xlToRigh

cLastRow = Cells(Rows.Count, "L").End(xlUp).Ro
For r = cLastRow To 1 Step -

' Set the cells need to do the jo

Set a = Cells(r, "M") ' Target for list without PO
Set b = Cells(r, "L") ' Cell containing PO# and lis

' Get the tex

a.Value = Right(b, Len(b) - Application.WorksheetFunction.Search(" ", b)

'Replace original data with new dat

b.Value =

Next

' Delete the "Helper' column, no longer neede

Columns("M:M").Selec
Selection.Delete shift:=xlLef

Application.ScreenUpdating = Fals

End Su

-------------------

I'm still learning this so someone will probably come up with somethin
better. Be sure to backup your workbook before trying this

--
Mark
-----------------------------------------------------------------------
Mark K's Profile: http://www.excelforum.com/member.php...fo&userid=1411
View this thread: http://www.excelforum.com/showthread.php?threadid=57384

Sorry, just saw where the PO# all started with Y851. Here's a fixed
version to take that into account.

Code:
--------------------
Sub poremove()

' Remove all before first space

Application.ScreenUpdating = False

' Insert 1 'Helper' column

Columns("M:M").Select
Selection.Insert shift:=xlToRight

cLastRow = Cells(Rows.Count, "L").End(xlUp).Row
For r = cLastRow To 1 Step -1

' Set the cells need to do the job

Set a = Cells(r, "M") ' Target for list
Set b = Cells(r, "L") ' Cell containing PO# and list

' Check for PO indicator

If InStr(1, Cells(r, "L"), "Y851") 0 Then

' Get the text

a.Value = Right(b, Len(b) - Application.WorksheetFunction.Search(" ", b))

'Replace original data with new data

b.Value = a

End If

Next r

' Delete the helper column, no longer needed

Columns("M:M").Select
Selection.Delete shift:=xlLeft
Range("L1").Select

Application.ScreenUpdating = True

End Sub

--------------------


--
Mark K
------------------------------------------------------------------------
Mark K's Profile: http://www.excelforum.com/member.php...o&userid=14117
View this thread: http://www.excelforum.com/showthread...hreadid=573846

Thank you VERY much everyone, I will try these macros out first thing
in the morning! Hopefully one of them will be exactly what I need.

Thanks Again


Mark K wrote:
Sorry, just saw where the PO# all started with Y851. Here's a fixed
version to take that into account.

Code:
--------------------
Sub poremove()

' Remove all before first space

Application.ScreenUpdating = False

' Insert 1 'Helper' column

Columns("M:M").Select
Selection.Insert shift:=xlToRight

cLastRow = Cells(Rows.Count, "L").End(xlUp).Row
For r = cLastRow To 1 Step -1

' Set the cells need to do the job

Set a = Cells(r, "M") ' Target for list
Set b = Cells(r, "L") ' Cell containing PO# and list

' Check for PO indicator

If InStr(1, Cells(r, "L"), "Y851") 0 Then

' Get the text

a.Value = Right(b, Len(b) - Application.WorksheetFunction.Search(" ", b))

'Replace original data with new data

b.Value = a

End If

Next r

' Delete the helper column, no longer needed

Columns("M:M").Select
Selection.Delete shift:=xlLeft
Range("L1").Select

Application.ScreenUpdating = True

End Sub

--------------------


--
Mark K
------------------------------------------------------------------------
Mark K's Profile: http://www.excelforum.com/member.php...o&userid=14117
View this thread: http://www.excelforum.com/showthread...hreadid=573846