Separate Text from a cell by space into other cells
 

How i can separate Text from Cell into other cell by space
for example
Columne A Column B Column C Column D

Mr. Muhammad Mr. Muhammad Sajjad
Sajjad

How can i do this by using excel function
--
Sajjad

Separate Text from a cell by space into other cells
 

Hi,

Data|Text to columns
Select delimited
Next
Check 'Space'
Finish

Mike

"Sajjad" wrote:


How i can separate Text from Cell into other cell by space
for example
Columne A Column B Column C Column D

Mr. Muhammad Mr. Muhammad Sajjad
Sajjad

How can i do this by using excel function
--
Sajjad

Separate Text from a cell by space into other cells
 

Suggest that you try Data Text to Columns functionality
Select the source col*, then click Data Text to Columns
Choose Delimited Check "Space" Finish
*do ensure that adjacent cols to the right are empty first
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"Sajjad" wrote:
How i can separate Text from Cell into other cell by space
for example
Columne A Column B Column C Column D

Mr. Muhammad Mr. Muhammad Sajjad
Sajjad

How can i do this by using excel function

Separate Text from a cell by space into other cells
 

But i have to da this every time and have to be very carefull about spaces in
different rows, i have to do this work thousand of rows (Around 36000 rows)

if some formula help me
--
Sajjad


"Max" wrote:

Suggest that you try Data Text to Columns functionality
Select the source col*, then click Data Text to Columns
Choose Delimited Check "Space" Finish
*do ensure that adjacent cols to the right are empty first
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"Sajjad" wrote:
How i can separate Text from Cell into other cell by space
for example
Columne A Column B Column C Column D

Mr. Muhammad Mr. Muhammad Sajjad
Sajjad

How can i do this by using excel function

Separate Text from a cell by space into other cells
 

On Wed, 15 Oct 2008 02:50:01 -0700, Sajjad
wrote:


How i can separate Text from Cell into other cell by space
for example
Columne A Column B Column C Column D

Mr. Muhammad Mr. Muhammad Sajjad
Sajjad

How can i do this by using excel function

The simplest method is to use the Data/Text to columns wizard; Delimited; with
<space as the delimiter.

There are formulas you could use, but much simpler to use a short User Defined
Function.

<alt-F11 opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, enter a formula of the type

=Prse($A1,Columns($A:A))

into an adjacent cell, and fill left as far as necessary. The COLUMNS function
will auto-increment so as to give appropriate "index" results into the string,
and should be entered as written.

The cell reference should be entered with the leading "$" so that when you fill
left, it will maintain the original column entry.

==================================
Option Explicit
Function Prse(str As String, Optional index As Long = 1, _
Optional separator As String = " ") As String
Dim aStr
aStr = Split(str, separator)
If index <= UBound(aStr) + 1 Then
Prse = aStr(index - 1)
End If
End Function
=====================================

--ron

Separate Text from a cell by space into other cells
 

When Max said:
"Select the source col*, ...
*do ensure that adjacent cols to the right are empty first",
he meant select the COLUMN, not select an individual CELL (unless that is
the only cell you want to process).
You can either select the whole column, or select a range (within the
column) which covers all the thousands of rows which you wish to process.
--
David Biddulph

"Sajjad" wrote in message
...
But i have to da this every time and have to be very carefull about spaces
in
different rows, i have to do this work thousand of rows (Around 36000
rows)

if some formula help me
--
Sajjad


"Max" wrote:

Suggest that you try Data Text to Columns functionality
Select the source col*, then click Data Text to Columns
Choose Delimited Check "Space" Finish
*do ensure that adjacent cols to the right are empty first
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"Sajjad" wrote:
How i can separate Text from Cell into other cell by space
for example
Columne A Column B Column C Column D

Mr. Muhammad Mr. Muhammad Sajjad
Sajjad

How can i do this by using excel function