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.
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 3EE | 999 Cann Hall Road | Leytonstone | London | E11 3EE | Example Row |
| 999 Lonsdale Avenue East Ham Newham London E6 3EE | |||||
| 999 Almond Close 82-84 Forest Road Walthamstow London E17 3EE | |||||
| 999 Margery Park Road Forest Gate London E7 9EE | |||||
| 999 High Street Wanstead London E11 2EE |
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.
- 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.