Hi,

I need help in re-organising name field, which is presently in lastname,
firstname followed by employee id format into firstname lastname format.

For eg, i have

'Was,Yu Wah 1320 0'

I need this to be re-arranged as

'Yu Wah Was'

The problem is not all the employees have two first names. So , there
are cases where the name is like

'Was,Yu 1340 0 '

and this is to be arranged as

'Yu Was'

Is this possible?

Regards

Govind.

As far as I know you will need VBA for something like that. The real trick is
that you need to find the comma and the first number. Then this becomes quite
easy. Here is some code. Record a macro (anything will do). Find the macro in
the VB editor. Tools Macro macros Step Into... Now delete all of the code and
paste this in...

Public Function FirstNumber(ByVal InputString As String) As Integer
Dim intCounter As Integer
Dim intStringLength As Integer
Dim intReturnValue As Integer

intReturnValue = -1
intStringLength = Len(InputString)

For intCounter = 1 To intStringLength
If IsNumeric(Mid(InputString, intCounter, 1)) Then
intReturnValue = intCounter
Exit For
End If
Next intCounter

FirstNumber = intReturnValue
End Function

This is a function taht you can use right in the cell formula that will
allow you to find the position of the first number in the cell (similar to
the way search and find return the position of the specified text)...
something like this...

=FirstNumber(A1)

Where A1 has the text

Was,Yu Wah 1320 0

it will return 13 which is the position of the 1. Now you can split your
names.

Or at least that is how I would do it...
--
HTH...

Jim Thomlinson


"Govind" wrote:

Hi,

I need help in re-organising name field, which is presently in lastname,
firstname followed by employee id format into firstname lastname format.

For eg, i have

'Was,Yu Wah 1320 0'

I need this to be re-arranged as

'Yu Wah Was'

The problem is not all the employees have two first names. So , there
are cases where the name is like

'Was,Yu 1340 0 '

and this is to be arranged as

'Yu Was'

Is this possible?

Regards

Govind.

Hi,

Thanks for that. But after lots of trial and error, was able to figure
out a formula solution for this and it worked.

I used

=MID(E1,FIND(",",E1)+1,(FIND("^",SUBSTITUTE(E1,"
","^",LEN(E1)-LEN(SUBSTITUTE(E1," ",""))-1))-(FIND(",",E1)+1)))&"
"&LEFT(E1,FIND(",",E1)-1)

Where E1 has the ''Was,Yu Wah 1320 0' '

Regards

Govind.


Jim Thomlinson wrote:
As far as I know you will need VBA for something like that. The real trick is
that you need to find the comma and the first number. Then this becomes quite
easy. Here is some code. Record a macro (anything will do). Find the macro in
the VB editor. Tools Macro macros Step Into... Now delete all of the code and
paste this in...

Public Function FirstNumber(ByVal InputString As String) As Integer
Dim intCounter As Integer
Dim intStringLength As Integer
Dim intReturnValue As Integer

intReturnValue = -1
intStringLength = Len(InputString)

For intCounter = 1 To intStringLength
If IsNumeric(Mid(InputString, intCounter, 1)) Then
intReturnValue = intCounter
Exit For
End If
Next intCounter

FirstNumber = intReturnValue
End Function

This is a function taht you can use right in the cell formula that will
allow you to find the position of the first number in the cell (similar to
the way search and find return the position of the specified text)...
something like this...

=FirstNumber(A1)

Where A1 has the text

Was,Yu Wah 1320 0

it will return 13 which is the position of the 1. Now you can split your
names.

Or at least that is how I would do it...