split numbers with space to multiple column

Hi to all,

i'm having some issues with using text to column to split my data to multiple column.
I think it may be due to multiple spaces in the value. Would appreciate if someone can advise me or help in it.

Basically my data is something like 3 3 3 9 5 4

so i would like each number to be separated and will eventually end up like this.

3

3

3

9

5

4

Please help! thanks!

Hi Sir,

May I know how do i replace two spaces with once space? I actually tried trim, using ablebits function as well but can't seems to do anything about the spaces.
The value just remain 3 3 3 9 5 4.

I have a whole lot of data that is like this so it will be difficult for me to manually remove the spaces. Is there any other way?

Thanks!

Originally Posted by Tony Valko

It may be that the spaces are actually char 160 spaces.

In Text to Columns try using the Other option and in the little box type ALT 0160 (hold down the ALT key then use the numeric keypad and type 0160).

The userform will then show you where the splits will appear.

Click Finish

Hi Tony,

I tried your method but nothing seems to appear. could it be the problem that i'm using a laptop that doesn't have numeric keypad?

Thanks!

Originally Posted by Tony Valko

Hmmm...

I don't have a laptop and don't know what process would be the equivalent of using the numeric keypad. On a desktop machine you MUST use the numeric keypad to produce the char 160 space.

Maybe this...

Test the cell to see if it actually does contain a char 160 space.

A1 = 3 3 3 9 5 4

Try this formula in B1:

=ISNUMBER(FIND(CHAR(160),A1))

This will return either TRUE (it does contain a char 160) or FALSE (it does not contain a char 160).

Hi Tony,

Yes it return TRUE. so should i try your method on a desktop?

Thanks!

Hi Tony,

Thanks for the help! i got it resolve =)

This formula will do the trick. Just put in a cell and drag right. Assumes the cell you want to split is A1

Formula:

Please Login or Register  to view this content.