Project functions for Custom fields: Left(String, Length) + Others

Hi MVPs,

I have got a requirement to derive a formula - Left( string, length ) and inserting into a [Textxx] Field for a specific type of string (alphanumeric) and where is the string is not equal to the aforesaid it should return blank or 0 or Null instead of #ERROR.

To make my requirement clearer, please see illustration below;

Text1 Field to contain the working formula	Name (reference field / data source)
Lookup Name field for string "FR101 or FR151" i.e. Left([Name],5), where string start is not FR (i.e. for the 1st 2 letters) return blank or 0 and not #ERROR	FR101 Start ESC build
Lookup Name field for string "FR101 or FR151" i.e. Left([Name],5), where string start is not FR (i.e. for the 1st 2 letters) return blank or 0 and not #ERROR	FR151 How to deduct from ESC data
Lookup Name field for string "FR101 or FR151" i.e. Left([Name],5), where string start is not FR (i.e. for the 1st 2 letters) return blank or 0 and not #ERROR	Run calculation
Lookup Name field for string "FR101 or FR151" i.e. Left([Name],5), where string start is not FR (i.e. for the 1st 2 letters) return blank or 0 and not #ERROR	RR101 Report on the Outcome of ESC

In addition, I would appreciate if the formula that will be derived to do this calculation can also lookup in an 'OR' instance i.e. lookup for string "FRxxx or RRxxx" in the same field.

Note, I have been able to do this in the past and currently with this IIf(Left([Name],4)="GATE","GATE",IIf(Left([Name],3)="KEY","KEY","")) to lookup or calculate for a custom field to return "GATE & KEY", you will notice that "GATE & KEY" are consistent words / set of letters but for the alphanumeric data that varies from row-to-row (i.e. the numeric part) is where my challenge is using the same formula??? FR101, FR151, RR101 etc...

I would be very grateful if you generous and kind MVPs out there can help.

I look forward to receiving your swift and candid responses (answers).

Thank you.

Kind Regards,

Olu

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Answer

Julie Sheets

You're welcome. I'm still not understanding what you are seeking. The formula I provided can be modified to search for any string and return any value.

Another option to test for multiple values is the switch function.

Switch(Instr([Name],"FR")>0,[Name],Instr([Name],"RR")>0,[Name],Len([Name])>0," ")

In the formula above, if the task starts with the letters FR, it will return the full task name, if the name starts with RR, it will return the full task name, if the task starts with neither RR or FR and the length of the name is greater than zero, it will return nothing.

Task Name= FR105             Returns FR105
Task Name = FR101            Returns FR101
Task Name = RR151            Returns RR151
Task Name = PQ564           Returns blank string

If you need to search for just 101 or 151 regardless of how the name starts, modify the formula.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer

Julie Sheets

Isn't that what the formula I gave you does?

It would return FR101, FR151, blank, and RR101. Project does not know that 101 is a number. It is in the Name field -- so it's a string to Project - not a number.

A couple of other options:

Do the 101 and 151 have meaning? In other words would your tasks of interest contain "101" or "151" in that case change the formula:

Switch(Instr([Name],"101")>0,[Name],Instr([Name],"151")>0,[Name],Len([Name])>0," ")

The formula will now return any task name containing either 101 or 151.

If the values are not in that pattern, you'll need to list every single possible part in the formula. Depending upon how many patterns you are searching for, this may be okay.

for example:
FR101, FR151, RR101, RR151. If you only have 4 parts, it shouldn't too bad.

Switch(Instr([Name],"FR101")>0,[Name],Instr([Name],"FR151")>0,[Name], Instr([Name],"RR101")>0,[Name],Instr([Name],"RR151")>0,[Name]Len([Name])>0," ")

To the best of my knowledge there is no formula that can discern the difference between "RR101" and "Run" if it is entered into the name field -- it's a string.

Have you though about a custom field with a list of parts that you can select? Use Text1, set up a lookup and then populate the lookup list with part data. You can then use filters to view those tasks.

I hope this helps.
Julie

Access Forum Top Contributors:

Project functions for Custom fields: Left(String, Length) + Others

Replies (7)

Question Info

Project functions for Custom fields: Left(String, Length) + Others

Replies (7) 

Question Info

Replies (7)