Splitting data in cell to multiple columns

[Nordin1980]

Hi,

I have this excel file with in one cell data that I want to split in multiple columns, I will attached the file (the divider is <BR><BR>) .

Can anyone help me to figure this out please.

Kind regards,
Nordin

[Cortlyn]

Hi Nordin

How/Where are you trying to split the data?

I have split it by the spaces - see attached.

Go to Data > Text to Columns > Delimited > Space (select what you want to split by)- use "Other" to make a selection by something not listed (insert what it is in the box)

Thanks

Cortlyn

[Nordin1980]

Thanks Cortlyn tried that already, but like I mentioned in my original request, I want to split the the data by the divider as mentioned above so every time this divider occurs I want the data generate a new column with the data that's following. If I do other I can't enter the full divider "<BR><BR>.

[Cortlyn]

Hi

You can also try (only if the data is consistent and always the same length.) to use the Text to Columns "Fixed Width" and put the markers in front and end of <BR><BR> (see attached).

Thanks

Cortlyn

[Glenn Kennedy]

In B2, copied across and down:

=TRIM(MID(SUBSTITUTE("<br><br>"&$A2,"<br><br>",REPT(" ",125)),125*COLUMNS($A:A),125))

Since you're in NL, you'll need ; instead of ,

[kvsrinivasamurthy]

If yo are interested in formula only
ARRy formula in K3, then drag across

=IFERROR(MID("<br><br>"&$A3,SMALL(IF(MID("<br><br>"&$A3,ROW(INDIRECT("1:"&LEN("<br><br>"&$A3)-1)),8)="<br><br>",ROW(INDIRECT("1:"&LEN("<br><br>"&$A3)-1)),""),INT((COLUMNS($K3:K3)+1)/2))+IF(ISODD(COLUMNS($K3:K3)),8,0),IF(ISODD(COLUMNS($K3:K3)),14,IF(ISERROR(SMALL(IF(MID("<br><br>"&$A3,ROW(INDIRECT("1:"&LEN("<br><br>"&$A3)-1)),8)="<br><br>",ROW(INDIRECT("1:"&LEN("<br><br>"&$A3)-1)),""),INT((COLUMNS($K3:K3)+1)/2)+1)),0,8))),"")

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

[Nordin1980]

Hi kvsrinivasamurthy,

Can you please place this into my worksheet it seems to not work when i try it?

[Glenn Kennedy]

I suggest you try the formula that I suggested. It's much simpler!!

[kvsrinivasamurthy]

File attached.

[kvsrinivasamurthy]

I have slightly changed Glenn Kennedy's formula to get data as in your file.TThis is more presice an very simple.
In B2 , then drag across

=TRIM(MID(SUBSTITUTE($A2,"<br><br>",REPT(" ",125)&"<br><br>"&REPT(" ",125)),1+125*(COLUMNS($A:A)-1),125))

[Glenn Kennedy]

I really do not believe that the OP wants the <br><br> bit returned. But we'll see.

[Nordin1980]

Thanks Glenn,

I have used your formula and that works best for me, I have now a different problem I have attached a new file maybe you can help me further.
The file I have has more cells with combined data the divider always remains "<br><br>" no the challenge is in the column that holds the date info, when I put in your formula it separates them correctly but the it returns the formatting as text and it seems that I can format that back to date format?
Next to that is that in the "Sales order" and "Invoices" column I want to formula to return only the numbers if possible same goes for invoice amount I only want to return the number.

Can you please have a look and let me know what I am doing wrong?

Thanks in advance.

Kind regards,
Nordin

[Glenn Kennedy]

You don't need 17,000 rows to make your point. for a few weeks I'm using a slow, metered connection. Almost always 10-20 rows is enough. In a column with numbers/dates ( I used column F)

=IFERROR(--TRIM(MID(SUBSTITUTE("<br><br>"&$F2,"<br><br>",REPT(" ",125)),125*COLUMNS($A:A),125)),"")

[Nordin1980]

Thanks Glenn,

This works perfect sorry for the huge file, by the way what about the other questions in my mail? about the invoice numbers how do I get rid of the word "Invoice #" in front of the number? and in the invoice amount column how to get rid of the word "EUR" behind the amount?

Thanks in advance,
Nordin

[Glenn Kennedy]

I spotted that bit afterwards. So , you just need to customise the formula slightly, to suit specific columns. Here it is again. In the case of column A, your data look like:

SO #29388<br><br>SO #29390

You have "SO #", as well as the <br> thing.

=IFERROR(--TRIM(MID(SUBSTITUTE("<br><br>"&SUBSTITUTE($A2,"SO #",""),"<br><br>",REPT(" ",125)),125*COLUMNS($A:A),125)),"")

The green bit substitutes SO # with nothing.
The red bit turns the text back into a number. Blanks are turned into #VALUE errors
The blue bit replaces the #VALUE errors with a blank
The black bit is just as before.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Nordin1980]

Thank you Glenn, much appreciated.

[Glenn Kennedy]

No problem...