Extracting name from text

[Minh_Tri_Do_Hang]

@Chr1sG I got some several name text field like this, which formula should i have to extract employee name only (name after )
- Tuấn 18-Sep-2023
- Hạnh 19-Sep-2023
- Nhung 19-Sep-2023
- Huấn 19-Sep-2023
- Huy 19-Sep-2023
- Thuần 19-Sep-2023
- Tuấn - 15:00 19-Sep-2023
7:45 - Hạnh 20-Sep-2023
- Huấn ⇄ Nhung 20-Sep-2023
- Thuần Hoài Nguyễn - 14:00 20-Sep-2023
- Huy An- 20-Sep-2023
- Nhung ⇄ Huấn 20-Sep-2023
- Tuấn - 20:00 → 21:45 20-Sep-2023
- Nhung ⇄ Huấn 20-Sep-2023

[Chr1sG]

I assume that you are talking about these lines being in a rich text field, right?
If so, you would need to write an automation to get the rich text field contents and then use regex to pattern match.
It looks rather complicated to me, since although the names are prefixed with the emoji, it’s not obvious to me how to determine where a name has ended.
For example, do you need to extract two names from this: Nhung ⇄ Huấn.

How is this data coming into Fibery? I wonder if it might be easier to preprocess the text in some way…

[Minh_Tri_Do_Hang]

That mean employee name swap shift to each other

Vào 13:35, T.4, 20 Th9, 2023 Chr1sG via Fibery Community <notifications@fibery.discoursemail.com> đã viết:

[Chr1sG]

Any comments to my other points?

[Minh_Tri_Do_Hang]

ah, seem I need to have more details about what I’m doing. Long time ago I have posted about tracking my employee shifts in fibery, and I feel it really hard to navigate direct in there, so I migrate the input stage in google calendar, and using emoji updating beyond on the change of original. Here’ s originial for each employee

Each day when somethings changes like: sick leave, vacation off, swap shifts … I put some emoji on that to know what change with original

Consumed the emoji structure will be: {Shift}{Shift Kind}{Details} - {Employee} - {Absence or Overtime}{Details}
Ex:
- Huy Thành Nguyễn- : meaning shift 1 (7:30am), assigned for Employee Huy Thành Nguyễn but Absence with no reason
7:45 - Nguyễn Hạnh : meaning shift 1 (7:30am) and employee Hạnh assigned and also come late at 7:45
- Nhung ⇄ Huấn: meaning orginal shift 's (10:00am) but Both employee agree to swap to each other: so Employee Nhung change from 10:00 to 7:30 shift and swap with Huấn Employee

I’m using 1 way google calendar sync to fibery, so when something update in google calendar, I can extract status for each type in fibery like Absence/ Overtime/ Come late/ Swap Shifts … So I need to extract the name field to specific fields: {People Assigned} | {Swap Status} | {Overtime Status} …

Not only Employee Name, I also wanna extract Shift: , Status: … to process in fibery… But feel hard to find the right formula to extract those, need advice from you @Chr1sG

[Chr1sG]

Well, it sounds like you would be best served by having several formula fields, each of which returns the relevant information, e.g.

If(Find(Name, "🕙") > 0, "morning shift", "afternoon shift")
returns a text string for the shift name

Find(Name, "⛔") > 0
returns true if the person didn’t show up

Find(Name, "🔁") > 0
returns true if there was a swap

etc.

Extracting the names is a little more complicated, since it seems like the name can be followed by a number, a hyphen or a ⇄ symbol.

So I would suggest using a regex formula to find the first name:

ReplaceRegex(
  Right(Name, Length(Name) - Find(Name, "👩‍🌾") - 2),
  If(Find(Name, "🔁") > 0, "(.*)( [⇄])(.*)", "(.*)( [0-9-])(.*)"),
  "\1"
)

then you can use another similar formula to find the second name, in the case there was a swap, e.g.

If(
  Find(Name, "🔁") > 0,
  ReplaceRegex(
    Right(Name, Length(Name) - Find(Name, "⇄") - 1),
    "(.*)( [0-9-])(.*)",
    "\1"
  ),
  ""
)

For any of the formulas that return a name, you might want to wrap them in Trim() to remove any leading or trailing spaces, just in case, e.g.

Trim(
  If(
    Find(Name, "🔁") > 0,
    ReplaceRegex(
      Right(Name, Length(Name) - Find(Name, "⇄") - 1),
      "(.*)( [0-9-])(.*)",
      "\1"
    ),
    ""
  )
)

[Chr1sG]

Bear in mind, I have attempted to provide some suggestions for formulas, but it is v difficult to check them against all your cases, and there might be alternative (better) ways of doing it.
You might want to visit regex101.com to play around with regex to see if you can debug any problems you encounter.

[Minh_Tri_Do_Hang]

I followed you guidance and modify a bit to make it works.
Another thing’s comming 's text convert:
In this case, I can extract a several fields
10:42 - Huy - 20:00 → 20:15 31-Aug-2023

• ⇄ 10:00 (just string, not date time format) ⇒ I wanna convert it to Dates time
• 10:42 ⇄ the time this employee came late ⇒ I wanna convert it to Dates time to calculate the Late Duration for each employee Each week
• 20:00 → 20:15 (duration for real overtime) ⇒ Can we convert it to Dates Range to calculate the Overtime Duration for eachweek
• 31-Aug-2023 (still string, not date time) ⇒ I wanna convert to 31/08/2023 (Date time) to trigger set relation automatic with your Periodic Database
  
  

To sum up, Do we have any way to convert those string (plain text) field into date time or duration ? I feel really limited about formula in fibery right now

[Chr1sG]

@Minh_Tri_Do_Hang

Do we have any way to convert those string (plain text) field into date time or duration ?

There is no way to convert strings to numbers using a formula.
And since a date/datetime is a specifically formatted set of numbers, it’s not possible.