Eliminate blanks before a charachter chain

[MónicaM]

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

[Tim Williams]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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]

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