String to be split

Y

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.
 
B

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
 
R

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
 
R

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
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top