Forum Discussion
Solved
Separating multiple lines in same cell into separate cells
Hi all, I have an urgent query that I have been working on for hours but to no avail.
I have a few hundred separate addresses in excel (say in the range A1-A200), arranged over 4-5 lines within each cell. An example is below in Cell A1;
A.N. Other
54 Bee Road
Anytown
Anywhere
LE4 6EY
Each of the 200 cells that contain the above addresses need each line of the address to be in a separate cell - so in the example above where this address is in Cell A1, I would need the first line (A.N. Other) to present in Cell B1, the 2nd line of the address (54 Bee Road) to present in Cell C1, etc all the way to there the 5th Line (LE4 6EY Postcode) to present in Cell F1.
The issue causing the difficulty -
Normally, 'Text to Column' should be able to resolve this. However, these addresses have been imported in csv format from another program and so even though the address is arranged over a number of lines, there is no a line break at the end of each line. Due to there being no line break, I am unable to use the 'Text to Columns' functionality to arrange the data into separate cells in the manner that I would like.
Apart from me manually going in and copying and pasting line by line (which will take me at least 1-2 days), is there an easy way of doing this? The key problem is linked to there being no line breaks at the end of each line and so I have been unable to do this with 'Text to Columns' functionality.
Any help on this would be gratefully appreciated.
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.
5 Replies
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.
