I need to remove an undefined amount of blanks in front of each name in
cells of a column in a Data Base in order to sort them.
I have a macro to separate the name from the surname as follows
Sub separa()
x = ActiveCell.Row
y = ActiveCell.Column
'Esto que sigue, toma el nombre y lo lleva 2 cols a la derecha
Do While Cells(x, y).Value < ""
Cells(x, y + 2).Value = "=MID(RC[-2],1,SEARCH("" "",RC[-2],1)-1)"
'Para tomar el apellido y llevarlo 3 cols a la derecha
Cells(x, y + 3).Value = "=MID(RC[-3],SEARCH("" "",RC[-3],1)+1,20)"
x = x + 1
Loop
End Sub
I am trying to fix it in order to do what I need. The above Macro looks
for the first blank in the chain and I need it to look for the first NOT
blank character in the chain. How can I say "different from blank" in
the function SEARCH? Or, is there another way to do it?. Please help me


--
MónicaM
------------------------------------------------------------------------
MónicaM's Profile: http://www.excelforum.com/member.php...o&userid=22523
View this thread: http://www.excelforum.com/showthread...hreadid=535163

Option Explicit

Sub separa()

Dim r As Range
Dim t
Dim i As Integer

Set r = ActiveCell

Do While r.Value < ""
t = r.Value
i = InStr(t, " ")
If i 0 Then
r.Offset(0, 2).Value = Trim(Left(t, i))
r.Offset(0, 3).Value = Trim(Right(t, Len(t) - i))
End If
Set r = r.Offset(1, 0)
Loop

End Sub


Tim

"MónicaM" wrote in message
...

I need to remove an undefined amount of blanks in front of each name in
cells of a column in a Data Base in order to sort them.
I have a macro to separate the name from the surname as follows
Sub separa()
x = ActiveCell.Row
y = ActiveCell.Column
'Esto que sigue, toma el nombre y lo lleva 2 cols a la derecha
Do While Cells(x, y).Value < ""
Cells(x, y + 2).Value = "=MID(RC[-2],1,SEARCH("" "",RC[-2],1)-1)"
'Para tomar el apellido y llevarlo 3 cols a la derecha
Cells(x, y + 3).Value = "=MID(RC[-3],SEARCH("" "",RC[-3],1)+1,20)"
x = x + 1
Loop
End Sub
I am trying to fix it in order to do what I need. The above Macro looks
for the first blank in the chain and I need it to look for the first NOT
blank character in the chain. How can I say "different from blank" in
the function SEARCH? Or, is there another way to do it?. Please help me


--
MónicaM
------------------------------------------------------------------------
MónicaM's Profile: http://www.excelforum.com/member.php...o&userid=22523
View this thread: http://www.excelforum.com/showthread...hreadid=535163

Maybe you could use Trim() in your formula:

Option Explicit

Sub separa()
Dim x As Long
Dim y As Long
x = ActiveCell.Row
y = ActiveCell.Column
'Esto que sigue, toma el nombre y lo lleva 2 cols a la derecha
Do While Cells(x, y).Value < ""
Cells(x, y + 2).FormulaR1C1 _
= "=MID(trim(RC[-2]),1,SEARCH("" "",trim(RC[-2]),1)-1)"
'Para tomar el apellido y llevarlo 3 cols a la derecha
Cells(x, y + 3).FormulaR1C1 _
= "=MID(trim(RC[-3]),SEARCH("" "",trim(RC[-3]),1)+1,20)"
x = x + 1
Loop
End Sub

"MónicaM" wrote:

I need to remove an undefined amount of blanks in front of each name in
cells of a column in a Data Base in order to sort them.
I have a macro to separate the name from the surname as follows
Sub separa()
x = ActiveCell.Row
y = ActiveCell.Column
'Esto que sigue, toma el nombre y lo lleva 2 cols a la derecha
Do While Cells(x, y).Value < ""
Cells(x, y + 2).Value = "=MID(RC[-2],1,SEARCH("" "",RC[-2],1)-1)"
'Para tomar el apellido y llevarlo 3 cols a la derecha
Cells(x, y + 3).Value = "=MID(RC[-3],SEARCH("" "",RC[-3],1)+1,20)"
x = x + 1
Loop
End Sub
I am trying to fix it in order to do what I need. The above Macro looks
for the first blank in the chain and I need it to look for the first NOT
blank character in the chain. How can I say "different from blank" in
the function SEARCH? Or, is there another way to do it?. Please help me

--
MónicaM
------------------------------------------------------------------------
MónicaM's Profile: http://www.excelforum.com/member.php...o&userid=22523
View this thread: http://www.excelforum.com/showthread...hreadid=535163

--

Dave Peterson