Pfthb

I have the below data set. I need to extract Unit codes UH 1,UH 2,UH 3 etc. I tried many formulas but was only able to extract the first code. How do I get to the Desired outcome in coloumn C, D & E.

enter image description here

A cell formula to do this would be heroic,the easiest way would be to use Data->Text to Columns and then re add the headers.
A longer term solution, to do it properly would be to use a short VBA function.

Functions splitter(str,n)
S=split(str)
Splitter=S(n)
End Function

To add that:

(1) Open the VBA editor (alt-f11)

(2) Insert a module

(3) type the above in

(4) Now the work sheet should recognise “splitter” as a function.

If it is essential to remove the brackets etc you can do that within the VBA script.
Now of course just type that into each of your output cells with word as the string and the n as the product number you need...
I’d change the row one to product number then...

=splitter($A3,B$1)

Copy paste paste that into your output (first product is n=0 so could could modify to

=splitter($A3,B$1-1)

Here are the "heroic" cell formulas. I inserted some columns to find the commas. It would be possible to combine the formulas but that would be too heroic.

• in cell B3:B7 =IFERROR(FIND(",",$A3),1000)


• in cell C3:E7 =IFERROR(FIND(",",$A3,B3+1),1000)


• in cell F3:F7 =LEFT($A3,B3-1)


• in cell G3:I7 =IF(B3<1000,TRIM(MID($A3,B3+1,C3-B3-1)),"n/a")

For each formula above, place the formula in the top-left cell and copy to the rest of the cells indicated.