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.
Thank you! I used the process and it worked for most of the cells. I was able to convert about 8k cells of 10k. The remaining cells have the line separating character on the second line and is not being converted to text. Please let me know if there is a solution or workaround for these remaining cells. Thank you again, this is incredibly helpful. I appreciate the assistance.
I'm afraid that doesn't help. You can attach a file in a private message to me (click on my user picture)
