Extracting data between [ ] 1

[JetRamsey]

Here's the data format:

[32] Event linked to [Warehouse] [BB16027901]

I'm trying to figure out a way to isolate BB16027901 above and I can't do it with a mid() formula since the database has changed. As seen above there are 3 sets of [ ]. In the first set, the number is 32, which is two digits. Unfortunately, the number can be one, two of three digits. In the [Warehouse] area, the number of characters also differs since the location name [Warehouse] can change. For the third set of [ ], this will always be a 10 character event number and that's what I would like to isolate so I can create a hyperlink to another event. Any thoughts? Thanks!

 

[AndyMc]

Try:

left(Split({yourFieldName},"[")[3],10)

 

[JetRamsey]

Thanks Andy, It did not work, but I changed your [3] to a [4] and the correct result appeared. If you know why the [4] works instead, please let me know.

 

[AndyMc]

It means I can't count

The formula breaks the string into segments based on the '[' character. So the segment that gets you to your substring is the fourth, not the third.

I'm glad you got it working!

-Andy