macro to position cursor to the next character

[ashley789]

hello

does someone know a macro that can position
the cursor the next letter or number.
i have a text file that has the raw info
like this :

abcd_ _ efg_ _ _ ab
ab_ _ _ _ _ _ gh

i want to skip the spaces and have excel
put "abcd" in cell a1 , "efg" in cell b1, "ab"in cell c1. The next line of information is entered with "ab" in cell a2 and cell b2 is entered with "gh".

Because the number of spaces varies between
each information and in each row, I can't the fixed width function to have excel import based on defined columns.

any help would be greatly appreciated.

ashley

 

[Juan Pablo González]

Ashley, hi, try this macro. It will work in XL 2000 or XP, since I used the Split function, that was not available in XL 97. If you have that, well, we need to create a custom Split for you (This was asked today BTW)

I assumed your data starts in row 1, and started parseing from that row.

Run the Test sub.<pre>Sub Test()
Dim i As Long
Dim FName As Variant
Dim FRange As Variant
FName = Application.GetOpenFilename("*.txt,*.txt")
If FName = False Then Exit Sub
Workbooks.OpenText FName, xlWindows, 1, xlFixedWidth, xlTextQualifierNone, FieldInfo:=Array(0, 1)

For i = 1 To Range("A65536").End(xlUp).Row
FRange = Parse(Cells(i, 1))
Cells(i, 1).Resize(1, UBound(FRange, 1)) = FRange
Next i
End Sub

Private Function Parse(ByVal Text As String) As Variant
Dim Ar As Variant
Dim Ans() As Variant
Dim i As Integer
Dim Ctr As Integer
Ar = Split(Text, " ")
For i = LBound(Ar) To UBound(Ar)
If Len(Ar(i)) > 0 Then
Ctr = Ctr + 1
ReDim Preserve Ans(1 To Ctr)
Ans(Ctr) = Ar(i)
End If
Next i
Parse = Ans
End Function</pre>

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-09-03 15:44

 

[Juan Pablo González]

Oh, another thing, the macro, where to put it. You can just put it in a new workbook, go to Visual Basic (Alt F11), insert a module, and paste the code there. Then, run the Test sub.

 

[Derek]

Hi there
Perhaps you could also try from the toolbar:
Data
Text to Columns
Delimited
Check against "Space"
Check against "Treat consecutive delimiters as one"
regards
Derek

 

[ashley789]

juan,

can you show step by step on how to create
split function - i have excel 97 version.
i read the posting and wasn't able to
follow it thru.

thank you

On 2002-09-03 15:43, Juan Pablo G. wrote:
Ashley, hi, try this macro. It will work in XL 2000 or XP, since I used the Split function, that was not available in XL 97. If you have that, well, we need to create a custom Split for you (This was asked today BTW)

I assumed your data starts in row 1, and started parseing from that row.

Run the Test sub.<pre>Sub Test()
Dim i As Long
Dim FName As Variant
Dim FRange As Variant
FName = Application.GetOpenFilename("*.txt,*.txt")
If FName = False Then Exit Sub
Workbooks.OpenText FName, xlWindows, 1, xlFixedWidth, xlTextQualifierNone, FieldInfo:=Array(0, 1)

For i = 1 To Range("A65536").End(xlUp).Row
FRange = Parse(Cells(i, 1))
Cells(i, 1).Resize(1, UBound(FRange, 1)) = FRange
Next i
End Sub

Private Function Parse(ByVal Text As String) As Variant
Dim Ar As Variant
Dim Ans() As Variant
Dim i As Integer
Dim Ctr As Integer
Ar = Split(Text, " ")
For i = LBound(Ar) To UBound(Ar)
If Len(Ar(i)) > 0 Then
Ctr = Ctr + 1
ReDim Preserve Ans(1 To Ctr)
Ans(Ctr) = Ar(i)
End If
Next i
Parse = Ans
End Function</pre>

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-09-03 15:44

 

[Juan Pablo González]

Ashley, I would suggest the other approach, using Text To Columns checking "Treat consecutive delimiters as one" in the second step. That would be far more efficient than my macro approach.