Text to columns

[Qros777]

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 ?

[#2 · Accepted solution]

@Qros777 

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 >.

[#3]

@Hans Vogelaar

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.

[#4]

@Qros777 

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.

[#5]

@Hans Vogelaar 

THANKS Hans !! it worked :))

I wish you a nice Sunday !! 

Greetings Qros 

[#6]

@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.