SOLVED

How to extract the bigger number from the first bracket?

Copper Contributor

Hi!

 

May I ask if it's possible to get the expected result below? Unsure if I should use regexextract. I can get a number but not the expected one.

 

Here's the screenshot

螢幕截圖 2022-07-22 下午2.10.27.png

Here's the link:
https://1drv.ms/x/s!Ar0aOsqJ5C6FlEdRYFGV2cq6vIjA?e=C7UJQ7

 

Thanks,
Cheryl

4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Cheryl 

=IFERROR(TRIM(MID(A2,SEARCH("[0-",A2)+3,FIND(" Booking]",A2,SEARCH("[0-",A2)+3)-(SEARCH("[0-",A2)+3)-5)),0)

Maybe with this formula.

extract.JPG 

Hi @Cheryl

 

I would use Power Query for this task. Load the source table in PQ and get the results in a separate table. Attached please find an example. You just need to right click in the green result table and choose "Refresh", if you have added more records in your source table.

  

@OliverScheurich Yes it works!! Thank you so much!!

Thank you!! I've never used PQ and will explore more about it. Interesting to know!
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Cheryl 

=IFERROR(TRIM(MID(A2,SEARCH("[0-",A2)+3,FIND(" Booking]",A2,SEARCH("[0-",A2)+3)-(SEARCH("[0-",A2)+3)-5)),0)

Maybe with this formula.

extract.JPG 

View solution in original post