Forum Discussion
Separating multiple lines in same cell into separate cells
I_need_help_544 Are you absolutely sure there are no line feed characters (CHAR(10)) used to separate each line? Can you do a quick test to confirm this? For example, in cell B2 try the following:
=SUBSTITUTE(A2, CHAR(10), ";")If the above formula returns the address with each line separated by semi-colons, then the original address does in fact contain line feed characters, and the Text to Columns feature can be used by selecting "Other" delimiter and pressing Ctrl+J to specify the line feed character.
If there are truly no line feed characters present, you will need to identify what character(s) are being used to create each line break. For example, if the address beginning with "A.N. Other" is in cell A2, the 11th character should be the line feed character, in which case you could try the following formula to reveal the character code:
=CODE(MID(A2, 11, 1))Or, if you have Excel for MS365, the following array formula may help by identifying each and every character in the address with their corresponding character codes:
=LET( arr, MID(A2, SEQUENCE(, LEN(A2)), 1), VSTACK(arr, CODE(arr)) )These are merely suggestions to help identify the character(s) being used to separate each line. Without access to your actual file, I'm afraid it's the best I can offer at this time.
Could you attach a small sample workbook with a few of such cells (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Thank you for your response.
I can't see how to attach my excel file so please see below table for what I'm after and an example in the first row (addresses are fictitious);
Full Address before any separating of lines Address Town County Postcode 999 Cann Hall Road
Leytonstone
London
E11 3EE999 Cann Hall Road Leytonstone London E11 3EE Example Row 999 Lonsdale Avenue
East Ham
Newham London
E6 3EE999 Almond Close
82-84 Forest Road
Walthamstow London
E17 3EE999 Margery Park Road
Forest Gate
London
E7 9EE999 High Street
Wanstead
London
E11 2EEI_need_help_544 Are you absolutely sure there are no line feed characters (CHAR(10)) used to separate each line? Can you do a quick test to confirm this? For example, in cell B2 try the following:
=SUBSTITUTE(A2, CHAR(10), ";")If the above formula returns the address with each line separated by semi-colons, then the original address does in fact contain line feed characters, and the Text to Columns feature can be used by selecting "Other" delimiter and pressing Ctrl+J to specify the line feed character.
If there are truly no line feed characters present, you will need to identify what character(s) are being used to create each line break. For example, if the address beginning with "A.N. Other" is in cell A2, the 11th character should be the line feed character, in which case you could try the following formula to reveal the character code:
=CODE(MID(A2, 11, 1))Or, if you have Excel for MS365, the following array formula may help by identifying each and every character in the address with their corresponding character codes:
=LET( arr, MID(A2, SEQUENCE(, LEN(A2)), 1), VSTACK(arr, CODE(arr)) )These are merely suggestions to help identify the character(s) being used to separate each line. Without access to your actual file, I'm afraid it's the best I can offer at this time.
- I think that this matter is now resolved!
Hans, I was just about to send you the spreadsheet but thought to try out DJ Clements's advice and tried suggestion 1, which showed that there were indeed semi colons at the end of each line, so this got me thinking as to why the 'Text to Columns' functionality wouldn't work. The only conclusion that I came to was that I was using a CSV instead of an excel file. I therefore saved the CSV file as an excel file, tried the 'Text to Column' functionality and voila it now works!!
Hans and DJ Clements, thank you both so very much, you have saved me upwards of 2 days of time.
I'm afraid that doesn't help. You can attach a file in a private message to me (click on my user picture)
