How to extract a specific number from column?

[EliGohar]

Hello experts,

I have the following values in a column:

1 - Eli

1 - Eli

1 - Eli, 2 - David, 3 - John, 4 - Steve2

1 - Eli, 2 - Jack, 3 - Eli43

1 - Eli, 2 - Pamela, 3 - Ron, 4 - Michael, 5 - Karen, 6 - Peter, 7 - Eli55

What I'm trying to extract is the last part, the number before the '-' sign.

It means the expected output for the example above should be:

1

1

4

3

7

Can you help me with this?

Thanks,

Eli

[Andrei_Cusnir · Accepted solution]

You can use a combination of SubField() function and another nested SubField() function. For example we have the data:

Now you can use the expression:

=SubField(SubField(Data, ',',-1), ' - ', 1)

The inner SubField() function will split the values based "," delimiter and will return the last value. The outer SubField() function will split again the already split text based on " - " delimiter and will return the first value. So the output is:

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

[EliGohar]

Amazing! Thanks @Andrei_Cusnir