String Split to Columns

Clear as mud...

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

OK. In B2, copied across and down:

=IFERROR(LOOKUP(10^10,--MID($A2,FIND(B$1,$A2)+1,{1,2,3,4,5})),"")

Take the example of what happens in cell D4.

A4 B9 C2 D1 E1 F2


the formula looks for the position of C (the 7th character) and starts from the 8th character (the +1). It then assembles an array of strings: the first with 1 character, the second with 2, and so on. The first is (minus the "") "2". the second is "2 " the third "2 D" and so on. One of the properties of the LOOKUP function is that it will return the last number it sees. That will be "2". The -- returns 2 as a number (as MID returns text that looks like a number).

Clear as mud??? To coin a phrase...

Thanks for the rep.

What is the MAXIMUM number of repetitions of any letter that can occur? I suspect that you'll need VBA for this....

I have found a way... providing that the maximum number, requested above, is small....

I have no idea what this means "Also, because probably of "" (at the end of formula) or I am not sure, when I count the the column cells, it count all lines even there is no data in cells
I use countif(range,"")"

try this.

This is likely much simpler to do via a macro. However I have worked out a formula that works with the following considerations:

• The string always follows the syntax of Alpha and 1 or 2 digit number then a space
• The repeated alpha sequences are consecutive within the string (IE: "A1 A2 A3 B1 C1", NOT "A1 B1 A2 C1 A3")

For my approach its doesnt matter how many times it repeats within a given string nor does it matter across the columns how many different strings you will look for (for ex A-F and A-Z should work with the same formula, as long as your layout is like it was in the sample).

I had to us a couple named ranges otherwise the formula would be super long due to repeated use of the same formula/results within other formulas. See name manager. I did this on Sheet2, left your sheet1 alone for comparison.

Hopefully it meets your needs.

Who are you talking to?

Originally Posted by ionelz

This is crazy question
In the file : ExtractNumbers.xlsx (attached file above) is anyway to UPDATE back the STRING if any modification in columns ?
So for example in cell A2B2 if I change from 1 to 6 can I get in an updated String to A6 C2 F5 ?

This is possible with VBA, because in a cell you can have a value or a formula, not both.

Originally Posted by ionelz

or Create another Column, String1 and concatenate back the columns A ..F to String1 column

Try, for example:
=TRIM(IF(B2="","",SUBSTITUTE(", "&B2,", "," "&B$1))&IF(C2="","",SUBSTITUTE(", "&C2,", "," "&C$1))&IF(D2="","",SUBSTITUTE(", "&D2,", "," "&D$1))&IF(E2="","",SUBSTITUTE(", "&E2,", "," "&E$1))&IF(F2="","",SUBSTITUTE(", "&F2,", "," "&F$1))&IF(G2="","",SUBSTITUTE(", "&G2,", "," "&G$1)))

Blessings!