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