break up text

C

Ciara

Hello,

I have pasted values from notepad into excel. I can't however get the values
to go into seperate cells. for example cell A1 has 7 numbers seperated by
spaces, the same for A2 and A3 etc.

2460070 -181.000000 4921.000000 587.723022 -1.758458
-1.446573 0.000000


Is there a way to get the numbers into seperate cells?

Thanks
 
G

Guest

Hi

Select the column/range and try Data|Text To Columns. Tell the wizard that
the text is delimted and select Space as the delimiter.
Save a copy of your data before you start!
 
C

Ciara

That worked perfectly - thanks!!

Andy B said:
Hi

Select the column/range and try Data|Text To Columns. Tell the wizard that
the text is delimted and select Space as the delimiter.
Save a copy of your data before you start!
Click to expand...
 
J

Joseph

Hi,
After you past the numbers that are separated by spaces you select th
cell that contains the 7 numbers then click on the Data option men
item then Text to Columns, follow the wizard and you it will se
wonders......

Cheer
 
K

Kishor

Hi, this is kishor my email id is [email protected]

Please try this fuction.

Function SeparateText(TextToSeparate, WithChr, TextNo)
p = 1
i = 1
j = 0

X = Application.WorksheetFunction.Trim(TextToSeparate) & WithChr

Do Until p = 0
p = InStr(i, X, WithChr, 1)
j = j + 1
NewStr = Application.WorksheetFunction.Trim(Right(Left(X, p - 1), p
- i))

If j = TextNo Then
Exit Do
End If
If p = 0 Then Exit Do
i = p + 1
Loop
SeparateText = NewStr

End Function
 
M

macropod

Hi Folks,

For Ciara's problem, I think Excel's standard Text to Columns function would
do just fine. Try:
Data|Text to Columns|Delimited and check both 'Space' and 'Treat Consecutive
Delimiters as One'.

Cheers
 
Top