I need to delete a string such as (Being Entered) (Basic Rigging) (Being Entered) (Dogging) from a cell that contains
RB (Being Entered) (Basic Rigging),DG (Being Entered) (Dogging)
Currently we dio this manually with the Find & Replace function using wildcards - Replace (*) with nothing thus giving us RB,DG
Can this be done in a formula so the user does not have to do this manually?
Last edited by jgupte; 05-29-2012 at 02:38 AM.
Is the data always the same i.e. numbers of characters in same place? If so this should do it.
=MID(A1,1,2)&MID(A1,35,3)
If I helped, please don't forget to add to my reputation. (click on the star below the post)
If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.
Failure is not falling down but refusing to get up.
Unfortunately not. The column has codes for various classes of licences, and in brackets the description of the licence. Most of the time there will be only one licence class in the field (with two strings of characters between brackets), but sometimes there will be two or more licence classes, with three or more strings.
Here is a sample of the data:
C2 (Current) (Slewing Mobile Crane (up to 20 tonnes)),DG (Current) (Dogging)
WP (Being Entered) (Boom Type Elevating Work Platform)
C6 (Being Entered) (Slewing Mobile Crane (up to 60 tonnes))
DG (Being Entered) (Dogging),C2 (Being Entered) (Slewing Mobile Crane (up to 20 tonnes))
As you can see, the first row and last row have the same licence classes, but in a different order.
This report is generated automatically based on entries made through the web by external customers, so it is not possible to get them to enter the data in the same order.
Also, some cells could have three or four licence classes.
What I need is to be able to specify a wild card in the replace function.
EG
Replace (CellRef, "(*)", "")
But this function sees the * as a character and not as a wild card.
Last edited by jgupte; 05-29-2012 at 01:12 AM.
I think you need VBA to do this. I recommend that you rename the thread to "VBA needed to find and replace text".
How do I rename the thread?
To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.
Columns("A:A").Select
Selection.Replace What:="@*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
How do I use the above code? (I do not know much about VB).
Q1: Does the ("A:A") refer to the cell range I want to change?
Q2: How do I call this routine? Do I put a formula in a cell and refer to this routine as a User Defined function?
Q3: Does the What:="@*" refer to the test I want to change (eg: change "(*)" to "") ?
Many thanks.
Jaya Sudha
Thanks for your response. Do you think you could give the whole of the code so that it can be pasted into the workbook of Jgupte.
Actually I figured it out.
This code is for a macro.
I have incorporated it into my full macro and it works well.
Many thanks.
Last edited by jgupte; 05-30-2012 at 08:26 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks