How to find text in text string


#1

what formula can I run against a text field that will display all text characters up to a “,”.

Example: TEXT1, TEXT2

Result: TEXT1


#2

Hi Arthur,

This should work:

LEFT({array field}, SEARCH(",", {array field})-1)

The LEFT function will display all characters up to some index, so you just use the SEARCH function to find the index of the comma, subtracting 1 if you don’t want to include it. It should return blank for a record with no comma.

Hope that helps!


#3

Thank you! Worked like a charm


#4

why does it apply a leading " - - example "Aby


#5

The " appears if you have commas in a lookup field (which you do) or if you have multiple lines in a field.
To adapt the formula to remove the ", use MID instead:
MID({array field}, 1, SEARCH(",", {arrayfield})-2)


#6

Thank you Andy!


#7

How do I reformat this ?
Ashbaugh, Cally
to
Cally Ashbaugh


#8

Assuming ‘Ashbaugh, Cally’ is in a field called {Name}, use this formula:

RIGHT(Name,LEN(Name)-FIND(' ',Name))&' '&LEFT(Name,FIND(',',Name)-1)


#9

Amazing! How do I learn to write these


#10

Start here.

Then go here.


#11

I have been trying variations on this formula for days now and keep getting 0 or #ERROR!. Will this not work when {array field} is a Lookup type field?


#12

If you wrap a lookup-type {Array Field} with ARRAYJOIN(), FIND() will work as expected. You probably should create a scratch formula field with the formula ARRAYJOIN({Array Field}) just to see what the resulting string looks like, as ARRAYJOIN() will alter the Lookup field somewhat. Alternatively, you can use a Rollup field instead of the Lookup.