Deleteing a portion of a cell.

[JokerFrowns]

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

 

[Bearacade]

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))

 

[JokerFrowns]

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.

 

[Joergen Bondesen]

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

 

[JokerFrowns]

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

 

[Bearacade]

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

....

 

[JokerFrowns]

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?

 

[Joergen Bondesen]

Hi JokerFrowns

Split Y815678954 Chair, Desk, Pencil for me, please.

Cell A1: Y815678954 Chair, Desk, Pencil.....
Cell ?: Y815 and cell ?: Chair, Desk, Pencil....

 

[Bearacade]

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.

 

[Joergen Bondesen]

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

 

[Mark K]

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

 

[Mark K]

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

 

[JokerFrowns]

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