Multiple rows in 1 cell to multiple cells

[robert26]

Hi!

I have table that contains cells with multiple rows. Is there a formula to split the cell into multiple cells so that each row is in different cell?
There is no common symbol in the end of each row to use good old 'text to columns'.

[AliGW]

You can do it in two steps.

1. In Find & Replace:
Find: ALT+010
Replace : |
2. Now use text to columns with the pipe symbol (|) as your delimiter.

[EdRooney]

Hi there

Have you tried LEFT, RIGHT and MID? If you search on youtube there are plenty of help examples of the functions on there.

Hope it helps

[robert26]

i hope I managed to add file. It changed to | but text to columns does not work

[robert26]

@Ed - those formulas wont work this time. data too different

[AliGW]

That's because of the {}, which Excel reads differently.

Do you need to keep the parenthesis?

[Fluff13]

Didn't refresh

[AliGW]

Pardon?

[EdRooney]

Try the attached.

I have given the example using the text I think you had before you did Find/Replace?

If your data is in a table it will autofill the appropriate formula down the column.

Hope it helps

[EdRooney]

@Ed - those formulas wont work this time. data too different

See example

[AlKey]

Is it something like this you are looking for?
Enter formula in A2 and drag formula down

Please Login or Register  to view this content.

v	A
1	{A} |{B} |{C} |{F} |
2	{A}
3	{B}
4	{C}
5	{F}
6

For your region you may need to replace all instances of comma with semi:colon ;

[Fluff13]

Pardon?

I hadn't refreshed the board before I posted.
After hitting Reply I realised that my suggestion didn't work, so removed it.

[AliGW]

OK - thanks for the clarification. Mystery solved!

[EdRooney]

@Robert26 - it would appear you have at least one solution to your problem. Please let us know how you get on

[robert26]

That's because of the {}, which Excel reads differently.

Do you need to keep the parenthesis?

Yes I do. This is just an example, but there would be some cells that begin with { and some that end with }. No cell would be {...}, but original data has them.

[EdRooney]

does each 'Row' of data have the same number of characters in? ie {A} being three?
IF so then, try the formula I posted

[robert26]

Try the attached.

I have given the example using the text I think you had before you did Find/Replace?

If your data is in a table it will autofill the appropriate formula down the column.

Hope it helps

Thank you for assistance, but as I mentioned, data are different and {A} section does not have strict digit number.

[robert26]

Is it something like this you are looking for?
Enter formula in A2 and drag formula down

Please Login or Register  to view this content.

v	A
1	{A} |{B} |{C} |{F} |
2	{A}
3	{B}
4	{C}
5	{F}
6

For your region you may need to replace all instances of comma with semi:colon ;

Thanks. Basically it works as I need. The only thing is that data are in column A (so I would need B1:B4 for A1 and then I cant really drag the formula for A2 (assuming data is somehow similar)

[robert26]

does each 'Row' of data have the same number of characters in? ie {A} being three?
IF so then, try the formula I posted

No, it may vary

[EdRooney]

If you go back to suggestion by AliGW... did that idea work? Perhaps change the "|" with another delimiter option such as comma or whatever?

[robert26]

Is there any formula somehow similar to =SUBSTITUTE to replace those mystical ALT+010 symbols?
So that when I get original data in column A, Excel automatically replaces them with "|"?

[robert26]

Yes that worked. "|" was fine. {} problem appeared after that.

[EdRooney]

Seriously, Find / Replace will do it!

I found this by doing a google search...


In the "Find what:" box you can enter the line-break code, which is character number 10.
To do this, press and hold the Alt key, and then enter the numbers 010 from the numeric part of the keyboard.
On my computer in the box there is then a small dot blinking when the cursor is still in the entry box.
To enter the numbers you need to use the numbers from the Num-lock part of the keyboard (with the numbers above the letters on your keyboard it doesn't work). If you have data imported from another source, sometimes the line breaks are also represented by character 13 instead of 10.

In the "Replace with:" dialog you can enter a space to have the line-breaks replaced with spaces. You can also leave that blank to have the line-breaks removed.

[EdRooney]

Can you post a small sample of the data you are referring to?

[robert26]

I know it works. THat is not the problem. My question is can it be automated. So that say data in column A have multiple rows, but in column B they aare replaced with |.

[robert26]

No, unfortunately data are confidential.

[EdRooney]

Ok, I appreciate the data is confidential. I guess what I was trying to understand was what the text looked like in the cells?

If the find replace works, but I take it the data can change from time to time?... then record a macro whereby you go through the steps you need to perform. Then you can create a button and assign the macro. Everytime you hit the button it will carry out the same function, but remember it will overwrite your existing, delimited data

[AliGW]

Seriously, Find / Replace will do it!

I found this by doing a google search...


In the "Find what:" box you can enter the line-break code, which is character number 10.
To do this, press and hold the Alt key, and then enter the numbers 010 from the numeric part of the keyboard.

This is exactly what I suggested in post #2 - you didn't need to do a Google search for it!!!

[robert26]

I can send original data example tomorrow. I am using different PC at the moment.
Data change. They are basically text exported from MS Outlook.
Unfortunately I understand next to nothing from Macroses

[EdRooney]

This is exactly what I suggested in post #2 - you didn't need to do a Google search for it!!!

I know, I was refocussing his mind!

[AliGW]

No, unfortunately data are confidential.

Provide dummy data that are representative of the real data and their layout.

Unless you are prepared to make an effort to meet your helpers halfway, why should they give freely of their time to help you? You are expecting a lot and giving very little.

[robert26]

Provide dummy data that are representative of the real data and their layout.

Unless you are prepared to make an effort to meet your helpers halfway, why should they give freely of their time to help you? You are expecting a lot and giving very little.

As I sai, I will provide data tomorrow.

[AliGW]

Good - thank you.

[robert26]

I know, I was refocussing his mind!

The problem was still unsolved because excel did not split the cell as I wanted. Then I had a follow-up question. There was no need to go back.

[AliGW]

We shall all wait for your sample workbook - there's no point in any further speculation until it's here.

[robert26]

See attachment.
ALT+010 works, but I am looking for formula so that it automatically substitutes this 'space' with '|' or whatever else.

[AliGW]

You need CHAR(10):

=SUBSTITUTE(A1,CHAR(10),"|")

[robert26]

Thank you for help That what I was looking for.

[AliGW]

You're welcome!

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