Forum Discussion
Text to columns
Hello guys,
my problem ist that "text to columns" isn't working properly.
It wont recognize the space between the text correctly. Am I doing something wrong ?
The spaces that aren't being recognized are probably non-breaking spaces.
Leave the check box for Leerezeichen ticked.
Also tick the check box for Andere and click in the box next to it.
Press Alt+0160 using the numeric keypad. This enters a non-breaking space.
Then click Weiter >.
5 Replies
Qros777 For better and dynamic results, I suggest using Power Query for the transformation, unless you are not a fan of PQ. You could bring in the data into PQ to split by position/space, merge the columns you require as a single record. The most important aspect is, if this is a recurring transaction, you wouldn't need to repeat the preceding steps; just bring in the messy data into the same excel sheet, refresh and you'd have your transformations.
The spaces that aren't being recognized are probably non-breaking spaces.
Leave the check box for Leerezeichen ticked.
Also tick the check box for Andere and click in the box next to it.
Press Alt+0160 using the numeric keypad. This enters a non-breaking space.
Then click Weiter >.
Thanks, i also think that it is a non-breaking space issue, because i copied the data from a website. Im on a Laptop (Dell XPS15) so i dont have a numeriy keypad, do you know how i can insert it.
You can select and copy one of the spaces that doesn't "work" in a cell.
Or:
Click the Start button (or press the Windows key), then select Windows Accessories > Character Map.
Click the N-Break Space, click Select, then click Copy.
You can now paste the non-breaking space into the box in the Text to Columns dialog.
