Cannot split data from a single row with carriage return

[franklegas]

Hi all guys,
I got a strange problem and I cannot find a solution anywhere.

I got an huge excel file where the data is formatted by carriage return in each row.
So i got a lot of values in the same row but I need to have all the data on different rows.

This is getting me crazy but I cannot find a way to "split" the value from a single row to multiple rows.

I attach here an example.

Thanks for your time

[AlKey]

Enter formula in A4 and copy formula across and down

Please Login or Register  to view this content.

But I think you would need a VBA solution for this

v	A	B	C
1	what i got
2	name1 name2	surname1 surname2	tel1 tel2
3
4	name1	surname1	tel1
5	name2	surname2	tel2

[Richard Buttrey]

Here's a simple macro that will do that.
Assumes the relevant cells are A2:C2

Please Login or Register  to view this content.

[franklegas]

Hi Alkey.
The real problem is that the values are charlenght variable .... it could be "surname1" or "surnamenew1" ..... that's why i'm getting mad!

My VBA skills are really really poor .....

[franklegas]

Hi Richard. Thanks for your answer.
If I need to loop this for all rows (for example a2,a3,an,b2,b3,bn,c1,c2,cn) ?
Moreover my values are not all "char(10)" .... it could be "surname1" or "surnamenew1" .... all the values change in my file.

How can I manage this ?

Thank you for your time

[Richard Buttrey]

Hi,

Your original only mentioned one cell in each column. The implication being that all the names etc. were contained therein, all separated by a Carriage Return Chr(10)

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy).

I'm not sure what you're getting at when you mention varying lengths for names. They are all presumably separated by a carriage return and the length of the name is irrelevant. I wasn't sure why you mentioned it when AlKey gave you a perfectly acceptable solution.

[franklegas]

Hi again,
that formula doesn't work for me :|

I copied in A4 but it reports an error....

I know .... I'm probably dumb :|

[Richard Buttrey]

Hi again,
that formula doesn't work for me :|

I copied in A4 but it reports an error....

I know .... I'm probably dumb :|

...as I said, upload the workbook so that we know exactly what we're working with.