Extract Data Query - Struggling

[Carl Stephens]

Hello All,

I am looking for a formula that will extra the numbers after the last close parenthesis and before the currency code. Note, that the total number of digits can change (from 3 to 6), the currency code can change and there can sometimes be multiple close parentheses in the string, as in the last example below. Is there a formula that will extract this data? I have tried many that I know of and have failed, hence I am here. Thank you for your time.

String 1. "Ocean - Head Butler (4 months on/ 2 months off) 3675 USD"
String 2. "Ocean - Butler (4 months on/ 2 months off) 2800 GBP"
String 3. "Ocean - Chef de Partie - Pastry (Bonus) (4 months on/ 2 months off) 13360 USD"

 

[shift-del]

Try Flash Fill.

 

[Carl Stephens]

Try Flash Fill.

OMG...sometimes, it is the simplest of things that make things happen. THANK YOU so much.

 

[Carl Stephens]

Try Flash Fill.

Out of curiosity, do you know of a formula for this? I ask as flash filling is great but….i need to lock the cells and that means the users won’t have the option to flash fill.

 

[shift-del]

That could be:

Mappe12
	B
1	3675
Tabelle1

Cell Formulas
Range	Formula
B1	B1	=--TEXTBEFORE(TEXTAFTER(A1,"off) ")," ")

 

[smozgur]

@Carl Stephens : I changed the marked solution with the actual answer post to the question (post #5). In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

--------

Also as another approach in Office 365: here are two different methods using different functions (new functions in 365) to extract data from a string like this - as it would also help future readers as an addition to @shift-del's solution above. Basically, we split the entire string using space as the delimiter with the TEXTSPLIT function, resulting in a two-dimensional array (a range in worksheet terms — one row, multiple columns in this example). From this array (range), we extract the required column using either the DROP/TAKE or CHOOSECOLS functions.

Book1
	A	B	C
1	String	Amount (DROP/TAKE)	Amount (CHOOSECOLS)
2	Ocean - Head Butler (4 months on/ 2 months off) 3675 USD	3675	3675
3	Ocean - Butler (4 months on/ 2 months off 2800 GBP	2800	2800
4	Ocean - Chef de Partie - Pastry (Bonus) (4 months on/ 2 months off) 13360 USD	13360	13360
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LET(arr, TEXTSPLIT(A2," "), cols, COLUMNS(arr), drop_curr, DROP(arr,,-1), amount, TAKE(drop_curr, ,-1), amount)
C2:C4	C2	=LET(arr, TEXTSPLIT(A2," "), cols, COLUMNS(arr), amount, CHOOSECOLS(arr,cols-1), amount)

Note about Office version I mentioned: I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Carl Stephens]

@Carl Stephens : I changed the marked solution with the actual answer post to the question (post #5). In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

--------

Also as another approach in Office 365: here are two different methods using different functions (new functions in 365) to extract data from a string like this - as it would also help future readers as an addition to @shift-del's solution above. Basically, we split the entire string using space as the delimiter with the TEXTSPLIT function, resulting in a two-dimensional array (a range in worksheet terms — one row, multiple columns in this example). From this array (range), we extract the required column using either the DROP/TAKE or CHOOSECOLS functions.

Book1
	A	B	C
1	String	Amount (DROP/TAKE)	Amount (CHOOSECOLS)
2	Ocean - Head Butler (4 months on/ 2 months off) 3675 USD	3675	3675
3	Ocean - Butler (4 months on/ 2 months off 2800 GBP	2800	2800
4	Ocean - Chef de Partie - Pastry (Bonus) (4 months on/ 2 months off) 13360 USD	13360	13360
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LET(arr, TEXTSPLIT(A2," "), cols, COLUMNS(arr), drop_curr, DROP(arr,,-1), amount, TAKE(drop_curr, ,-1), amount)
C2:C4	C2	=LET(arr, TEXTSPLIT(A2," "), cols, COLUMNS(arr), amount, CHOOSECOLS(arr,cols-1), amount)

Note about Office version I mentioned: I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you. I have updated my profile to show that I am using O365.

The below formula worked, only by default, it returns a text result rather than a number one, so I had to adjust it slightly by the second formula below, then it worked perfectly. Thank you so much for your support.
=LET(arr, TEXTSPLIT(A2," "),cols, COLUMNS(arr),amount, CHOOSECOLS(arr,cols-1),amount)
=LET(arr, TEXTSPLIT(A2," "),cols, COLUMNS(arr),amount, VALUE(CHOOSECOLS(arr,cols-1)),amount)

 

[smozgur]

Thank you. I have updated my profile to show that I am using O365.

Thanks for doing that!

The below formula worked, only by default, it returns a text result rather than a number one, so I had to adjust it slightly by the second formula below, then it worked perfectly. Thank you so much for your support.
=LET(arr, TEXTSPLIT(A2," "),cols, COLUMNS(arr),amount, CHOOSECOLS(arr,cols-1),amount)
=LET(arr, TEXTSPLIT(A2," "),cols, COLUMNS(arr),amount, VALUE(CHOOSECOLS(arr,cols-1)),amount)

Thanks for the feedback. Glad to hear it helped. These new functions are really useful ones.