Unconcatenate?

[689touch]

Attached is an extract of a list, I want to extract the 100 (pack of) into a separate column. The list is a long one and contains various pack quantities (pack of 50, pack of 10) for example. I want to be able to extract the quantity each time.

Is this possible?

Thank you

 

[RoryA]

Which version of Office do you have?

 

[689touch]

Which version of Office do you have?

Windows 11 Business

 

[Peter_SSs]

Windows 11 Business

The question was what version of Office do you have, not which version of Windows.

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

A couple of options depending on what functions you have available in your Excel version.

24 03 26.xlsm
	A	B	C
1	abc def (pack of 100) ghi	100	100
2	abc def (pack of 1000) aaa	1000	1000
Pack no

Cell Formulas
Range	Formula
B1:B2	B1	=--TEXTAFTER(TEXTBEFORE(A1:A2,")")," ",-1)
C1:C2	C1	=--REPLACE(LEFT(A1,FIND(")",A1)-1),1,FIND("(",A1)+8,"")
Dynamic array formulas.

 

[RoryA]

Which version of Office, not Windows, please. It will affect what solutions are available to you so ideally you should update your profile here to include that information.

 

[689touch]

The question was what version of Office do you have, not which version of Windows.

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

A couple of options depending on what functions you have available in your Excel version.

24 03 26.xlsm
	A	B	C
1	abc def (pack of 100) ghi	100	100
2	abc def (pack of 1000) aaa	1000	1000
Pack no

Cell Formulas
Range	Formula
B1:B2	B1	=--TEXTAFTER(TEXTBEFORE(A1:A2,")")," ",-1)
C1:C2	C1	=--REPLACE(LEFT(A1,FIND(")",A1)-1),1,FIND("(",A1)+8,"")
Dynamic array formulas.

This worked an absolute treat - thank you.

 

[Peter_SSs]

This worked an absolute treat - thank you.

You're welcome.
.. but which one(s) worked?
.. and what about your Excel version? Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) 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’)

 

[689touch]

You're welcome.
.. but which one(s) worked?
.. and what about your Excel version? Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) 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’)

Morning,
The TEXTAFTER / TEXT BEFORE worked.
Have updated profile.

Thank you

 

[Peter_SSs]

Cheers. Thanks for the confirmation.
.. and for updating your details.