Distribution of a string into columns without text

[baidya91]

How can I distribute PB4-440-20-560-25-785-30-AGP-875-35-1050-40-GP-1170 of Cell A1 into various columns such as B1, C1, D1, E1, F1, .....etc. without text strings such as AGP, PB4, GP etc.by using excel formula?

 

[XOR LX]

Hi,

Assuming your entry is in A1, array formula** in B1:

=IFERROR(0+LEFT(SUBSTITUTE(MID($A1,SMALL(IF(MMULT(N(ABS({45,52.5}-CODE(MID("-"&$A1,{0,1}+ROW(INDIRECT("1:"&LEN($A1))),2)))<={0,5}),{1;1})=2,ROW(INDIRECT("1:"&LEN($A1)))),COLUMNS($A:A)),99),"-",REPT(" ",99)),99),"")

Copy to the right until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[baidya91]

thanks

 

[XOR LX]

You're welcome!

Cheers