Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String to be split
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
String to be split
On 15 dic, 08:07, yagna wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
String to be split
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 (MVP - Excel) "yagna" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
String to be split
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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "yagna" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
String to be split
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 yagna wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split up delimited string & insert row below | Excel Programming | |||
Split string into seperate cells | Excel Programming | |||
Split a string | Excel Programming | |||
Using Split Function on String with no spaces | Excel Programming | |||
how to split a string and return array | Excel Programming |