Removing Text

  • Thread starter Thread starter The Report Guy
  • Start date Start date
T

The Report Guy

Hi,

There is a field in our system that groups together a list of products
called p_desc. Whomever build the system concatenates 3 fields together.
Fields: Group, Code, Desc

The way the data is stored is as follows:
Group 47: Transportation Services
Group 48: Communications
Group 25: Furniture And Fixtures

How do I remove the txt before before the :? I want my results to be as
follows:
Transportation Services
Communications
Furniture And Fixtures

Please help.

Thanks
 
you can use the RIGHT function to extract the data after the colon. To
determine how many characters to extract use the InStr formula to get the
character position of the colon, the LEN function to determine the length of
the string. Subtracting the character position of the colon from the length
of the string extracts just the text to the right of the colon. The trim
function removes any leading and trailing spaces.

Wherever "Group 47: Transportation Services" appears in the function below,
substitute the name of the field:

trim(right("Group 47: Transportation Services",len("Group 47: Transportation
Services")-Instr(1,"Group 47: Transportation Services",":")))
 
Thank you Kevin.

That is what I tried but did not know how to put it together :-(
 
Hi,

There is a field in our system that groups together a list of products
called p_desc. Whomever build the system concatenates 3 fields together.
Fields: Group, Code, Desc

The way the data is stored is as follows:
Group 47: Transportation Services
Group 48: Communications
Group 25: Furniture And Fixtures

How do I remove the txt before before the :? I want my results to be as
follows:
Transportation Services
Communications
Furniture And Fixtures

Please help.

Thanks
Click to expand...

You can actually save a step by using the Mid() function insetad of Right():
Mid() takes three arguments, a string, a position in that string, and a
length. But the third argument is optional, and if omitted will return the
rest of the string, whatever the length.

So you could extract this portion of the string with an expression

Mid([p_desc], InStr([p_desc], ": ") + 2)


John W. Vinson [MVP]
 
Back
Top