Extract text from string

[gazmoz17]

Hi,

Do you have any pointers for the above please? I've tried a combination of formulas and text to columns but I need something automated to make a control from a query.

Were going to start selling prods as kits so need to check all descriptions and refs are correct, so variables are feeding into an online calc correctly.

Product Code	Description		Product Code before bracket	Product code extract brackets	Description m2	Description grams "g"
CU10(450)	Cure It 10m2 Roofing Kit 450g (light duty)		10	450	10	450
CU10(600)	Cure It 10m2 Roofing Kit 600g (heavy duty)		10	600	10	600

Many Thanks

 

[Dan_W]

It depends on how you want to go about doing it - are you looking for a VBA-based solution, or formula/etc-based solution?

 

[JEC]

You could try this Regex UDF

Function jec(rng As Range) As Variant
 With CreateObject("vbscript.regexp")
   .Global = True
   .Pattern = "(^|\D+)(\d+)(\()(\d+)(\))(.+)(\s\d+)(m.+)(\s\d+)(g.*)"
   jec = Split(.Replace(Join(Application.Transpose(Application.Transpose(rng))), "$2 $4$7$9"))
 End With
End Function

Use as:

=jec(A2:B2)

 

[Peter_SSs]

I am pretty sure that this is not the answer to your problem. However, it does highlight the need to give sample data that shows the sort of variety that can occur in your data. Your two samples are very uniform.

gazmoz17.xlsm
	A	B	C	D	E	F	G
1	Product Code	Description		Product Code before bracket	Product code extract brackets	Description m2	Description grams "g"
2
3	CU10(450)	Cure It 10m2 Roofing Kit 450g (light duty)		10	450	10	450
4	CU10(600)	Cure It 10m2 Roofing Kit 600g (heavy duty)		10	600	10	600
Sheet1

Cell Formulas
Range	Formula
D3:D4	D3	=MID(A3,3,2)
E3:E4	E3	=MID(A3,6,3)
F3:F4	F3	=MID(B3,9,2)
G3:G4	G3	=MID(B3,26,3)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.