Remove text from a field to a specific length

[wendell42]

I have a text field that holds property legal description. At the beginning of the field it either says "sixteenth:" or "Subdivision:" then lists the legal description (this is how it comes from our CAMA software).

I would like to be able to remove the text as stated above. I can do it in two queries, one that states if it begins with sixteenth: and the other if it begins with Subdivision, but how do I exclude the text itself?

I want to append the text to a different table for use elsewhere in the office. Any help would be GREATLY appreciated.

Wayne

 

[Micron]

I want to append the text to a different table for use elsewhere in the office.

You want to append the words "sixteenth" or "Subdivision" to another table?

I would like to be able to remove the text as stated above. I can do it in two queries, one that states if it begins with sixteenth: and the other if it begins with Subdivision, but how do I exclude the text itself?

This part I don't get. If you can remove it with two queries have you not already excluded it?

 

[Trevor G]

Assuming you have created an Expression to show the 2 words then all you should need to do is remove the tick from the other field to not show the field then it is hidden.

 

[wendell42]

I just want to remove the text "sixteenth" where it appears at the beginning of the field. And do the same (remove) where "Subdivision" appears. Don't want that text in the field.

 

[LloydFinancials]

Sub FindReplace()
'Given that there is a space after the keywords
With Worksheets("Sheet1").Columns("A")
   .Replace What:="sixteenth ", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=False
   .Replace What:="subdivision ", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=False
End With
End Sub

 

[LloydFinancials]

I see I did not need the SearchOrder. Here it is revised.

Sub FindReplace()
'Given that there is a space after the keywords
With Worksheets("Sheet1").Columns("A")
   .Replace What:="sixteenth ", Replacement:="", MatchCase:=False
   .Replace What:="subdivision ", Replacement:="", MatchCase:=False
End With
End Sub

 

[Trevor G]

Then try this. Open the table with all the data in it. Then select the field you want to search through and use the Replace button. Type in the word you want to remove and select to search ANY PART OF THE FIELD that will delete the entries. Repeat for your second word.

 

[strive4peace]

you can run an update query to change the values. I would actually run 2 of them. This would be the first and then I think you can probably see what to do for your other term. If Trim chops the text back to 255, don't use it ... I think it is a safe function for long text but can't remember

BEFORE YOU RUN ANY ACTION QUERIES, BACKUP YOUR DATABASE

UPDATE [tablename] 
SET [fieldname] = Trim(MID([fieldname],len("sixteenth:")+1)) 
WHERE left([fieldname],len("sixteenth:") = "sixteenth:"

After you paste the SQL statement into the SQL view of a new query, change [tablename] and [fieldname] to your names. Then look at the design view of the query so you can see how easy it is to make update queries ... Then click ! to Run