String to be split

[yagna]

Hi,

I have the string in the Excel file where the
"\abc*aa**11111_aa*125.00*125.00*0.0\ " I need to split this data from the
long string stored in the cell. Sometime the above marked string is broken to
next row. how do I split this in macro & to find out when it is split in the
different but continuous row.

Split required are
Col A -111111_aa
Col B -125.00
col C-125.00
Col D-0.00

Thanks & regards,
yagna.

 

[Benito Merino]

Hi,

I have the string in the Excel file where the  
"\abc*aa**11111_aa*125.00*125.00*0.0\ " I need to split this data from the
long string stored in the cell. Sometime the above marked string is broken to
next row. how do I split this in macro & to find out when it is split in the
different but continuous row.

Split required are
Col A -111111_aa
Col B -125.00
col C-125.00
Col D-0.00

Thanks & regards,
yagna.

Hello.

This macro can help you:

Sub split()
textstring = "\abc*aa**11111_aa*125.00*125.00*0.0\ "
a = split(Replace(textstring, "\", ""), "*")
For cont = LBound(a) To UBound(a)
On Error Resume Next
If IsNumeric(Left(a(cont), 1)) Then
desr = desr + 1
With ActiveCell.Offset(desr, 0)
.NumberFormat = "@"
.Value = a(cont)
End With
End If
Next
End Sub

Regards,

Benito
Barcelona

 

[Rick Rothstein]

Here is another way to do what you want...

Sub SplitTextString()
Dim X As Long, TextString As String, Parts() As String
TextString = "\abc*aa**11111_aa*125.00*125.00*0.0\ "
Parts = Split(Trim(Replace(TextString, "\", "")), "*")
For X = UBound(Parts) - 3 To UBound(Parts)
Cells(1, X - 2).Value = Parts(X)
If X > 3 Then Cells(1, X - 2).NumberFormat = "0.00"
Next
End Sub

 

[Rick Rothstein]

I forgot to generalize all the code; use this macro instead of the one I
posted before...

Sub SplitTextString()
Dim X As Long, TextString As String, Parts() As String
TextString = "\abc*aa**11111_aa*125.00*125.00*0.0\ "
Parts = Split(Trim(Replace(TextString, "\", "")), "*")
For X = UBound(Parts) - 3 To UBound(Parts)
Cells(1, X - UBound(Parts) + 4).Value = Parts(X)
If X > UBound(Parts) - 3 Then Cells(1, X - _
UBound(Parts) + 4).NumberFormat = "0.00"
Next
End Sub

 

[keiji kounoike]

I wonder what "*" means and what "Sometime the above marked string is
broken to next row" means. Is the marked string, in your case, "aa",
"111111_aa", "125.00", "125.00" and "0.0"? Is the "*" just a delimiter
or something else? Do you just want to find the strings of 111111_aa,
125.00 and 0.00 in a long string in the cells or something else?

Keiji