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
