Text to colums

[Bonnie]

I'm trying to split text in 1 column into several colums,
I'm using deliminated and then space. The problem is the
data has more than 1 space in it, I would like it to
split the data to the first space and not the consecutive
spaces. Is there a way to do that?

thanks

 

[Dave R.]

Try these formulas;

You want to split up from A1

AX 3040 4050 405

into 2 columns,

AX

3040 4050 405

...

In B1,
=LEFT(A1,FIND(" ",A1)-1) to get "AX"
and
=RIGHT(A1,LEN(A1)-LEN(B1)-1) to get "3040 4050 405"

 

[Dennis]

As you go thru the wizard you'll be able to point and click the column
seperators away or create them. Very handy tool.

Dennis
==========

 

[Dave R.]

Where is that? The only time I see anything like that is if you use
fixed-width instead of delimited. Unless her spaces are in the same position
each time that will not work.

 

[Dennis]

Ah, my bad, I didn't pick up on the delimited.

Where is that? The only time I see anything like that is if you use
fixed-width instead of delimited. Unless her spaces are in the same position
each time that will not work.

 

[Bonnie]

Yes, that is exactly what I am trying to do. I put the
formula into B1 however nothing happened??

I need the data in A1 to be split into A1 and A2.

Thanks

 

[Dave R.]

Well the issue is that since you have multiple spaces and don't want a new
column for each space it finds (or spaces + 1 as it is), you may need to use
a formula to do it. Whatever you have in A1 will remain in A1. You'll have 2
other cells, say B1 and C1, that contain the finished products.

Again example, in A1: AX 2303 405
in B1: =LEFT(A1,FIND(" ",A1)-1)
anywhere but A1 or B1: >=RIGHT(A1,LEN(A1)-LEN(B1)-1)

Once you put those formulas in and have the results they return, you can
copy>paste special>values .

BTW - even a text-to-columns wouldn't split it into A1 and A2, that would be
text-to-rows, and I don't know if one can do that with a stock Excel.