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 output in column C, D & E ?
Add a comment
|
2 Answers
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)
Comments
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.
1 Comment
RichardBJ
Indeed, I do often insert construction columns myself too when passing Excel strings (especially people's names). Your method also has the advantage it works in any old Excel xlsx not just macro enabled ones, but if the user potentially has many more products (I have known some stores that do) I thought a VBA might be useful.