Hi Everyone,

I need help with a certain problem i'm stuck at in vba

The situation is like this:

I have a cell with the value
"1.0-73|Event_Id|DealIdFO|Context|Validation_Id|Validat ionStatus_Id|Validated|EventDate|EventType|Categor y|InternalStatus|AccountingFlag|Action|FODeal_Id_F irst|BODeal_Id|Description|Users_Id_FO|BOCaptureDa te|Entity_Id|BODeal_Id_Father|BODeal_Id_First|FODe al_Id_Father|Version|CorporateActionKey_Id|Securit ies_Id|Securities_ShortName|SecuritiesType|LegSecu rities_ShortName|LegSecuritiesType|Securities_Pair s_ShortName|Securities_Options_ShortName|Securitie s_Futures_ShortName|Definition|OptionStrike|Maturi ty|LastTradeDate|DealLeg|Folders_Id|Amount|Princip alCur1|PrincipalCur2|ForwardAmountCur1|ForwardAmou ntCur2|Currencies_Id|Currencies_Id_1|Currencies_Id _2|DifferentialCur1|DifferentialCur2|StartDate|End Date|Rate|FloatingRates_Id|AdditiveMargin|Multiply Margin|Principal|Strike|PaymentDate|Pairs_Id|Index ation|Quantity|Price|ThirdParty_Id_Cpty|DealType|D ealSubType|ClientMargin|OptionType|EventCustomType _Id|EventCfg_Id|VersionNumber|ActionDate|OriginalE ventDate|ActionDetail|Event_Id_Previous|CollatAgrt _Id|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X| X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X| X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X| |||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||50|||||||||||||||||||||||| |||||||||||||||||||||||||||||||||||||||||||||||||1 |13|0|6|2|3|4|5|7|8|9|10|11|12|14|15|16|17|18|19|2 0|21|22|23|24|25|26|27|28|29|30|31|32|33|34|35|36| 37|38|39|40|41|42|43|44|45|46|47|48|49|50|51|52|53 |54|55|56|57|58|59|60|61|62|63|64|65|66|67|68|69|7 0|71|72||A|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X |X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X |X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X |X|X|X|"

As you can see, there is alot of data in one particular field and it is not
typed manually as it is extracted from the database.

I would like to know if there is a coding that allows me to be able to
separate this value in the cell into different rows separated by the " | "
sign and to paste only numeric values on the next sheet.

Would be really great if you can help me out!

F1 - Split()

Since your requirement is

separate this value in the cell into different rows

with the data in cell A1 try the below macro; which will extract the
information to Column B

Sub Macro()
Dim strData As String, arrData As Variant
Dim lngRow As Long
strData = Trim(Range("A1"))
arrData = Split(strData, "|")
For lngRow = 1 To UBound(arrData)
Range("B" & lngRow + 1) = arrData(lngRow)
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"mEl" wrote:

Hi Everyone,

I need help with a certain problem i'm stuck at in vba

The situation is like this:

I have a cell with the value

"1.0-73|Event_Id|DealIdFO|Context|Validation_Id|Validat ionStatus_Id|Validated|EventDate|EventType|Categor y|InternalStatus|AccountingFlag|Action|FODeal_Id_F irst|BODeal_Id|Description|Users_Id_FO|BOCaptureDa te|Entity_Id|BODeal_Id_Father|BODeal_Id_First|FODe al_Id_Father|Version|CorporateActionKey_Id|Securit ies_Id|Securities_ShortName|SecuritiesType|LegSecu rities_ShortName|LegSecuritiesType|Securities_Pair s_ShortName|Securities_Options_ShortName|Securitie s_Futures_ShortName|Definition|OptionStrike|Maturi ty|LastTradeDate|DealLeg|Folders_Id|Amount|Princip alCur1|PrincipalCur2|ForwardAmountCur1|ForwardAmou ntCur2|Currencies_Id|Currencies_Id_1|Currencies_Id _2|DifferentialCur1|DifferentialCur2|StartDate|End Date|Rate|FloatingRates_Id|AdditiveMargin|Multiply Margin|Principal|Strike|PaymentDate|Pairs_Id|Index ation|Quantity|Price|ThirdParty_Id_Cpty|DealType|D ealSubType|ClientMargin|OptionType|EventCustomType _Id|EventCfg_Id|VersionNumber|ActionDate|OriginalE ventDate|ActionDetail|Event_Id_Previous|CollatAgrt _Id|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X| X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X| X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X| |||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||50|||||||||||||||||||||||| |||||||||||||||||||||||||||||||||||||||||||||||||1 |13|0|6|2|3|4|5|7|8|9|10|11|12|14|15|16|17|18|19|2 0|21|22|23|24|25|26|27|28|29|30|31|32|33|34|35|36| 37|38|39|40|41|42|43|44|45|46|47|48|49|50|51|52|53 |54|55|56|57|58|59|60|61|62|63|64|65|66|67|68|69|7 0|71|72||A|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X |X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X |X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X |X|X|X|"

As you can see, there is alot of data in one particular field and it is not
typed manually as it is extracted from the database.

I would like to know if there is a coding that allows me to be able to
separate this value in the cell into different rows separated by the " | "
sign and to paste only numeric values on the next sheet.

Would be really great if you can help me out!

On Thu, 13 Aug 2009 20:43:01 -0700, mEl wrote:

Hi Everyone,

I need help with a certain problem i'm stuck at in vba

The situation is like this:

I have a cell with the value
"1.0-73|Event_Id|DealIdFO|Context|Validation_Id|Validat ionStatus_Id|Validated|EventDate|EventType|Categor y|InternalStatus|AccountingFlag|Action|FODeal_Id_F irst|BODeal_Id|Description|Users_Id_FO|BOCaptureDa te|Entity_Id|BODeal_Id_Father|BODeal_Id_First|FODe al_Id_Father|Version|CorporateActionKey_Id|Securit ies_Id|Securities_ShortName|SecuritiesType|LegSecu rities_ShortName|LegSecuritiesType|Securities_Pair s_ShortName|Securities_Options_ShortName|Securitie s_Futures_ShortName|Definition|OptionStrike|Maturi ty|LastTradeDate|DealLeg|Folders_Id|Amount|Princip alCur1|PrincipalCur2|ForwardAmountCur1|ForwardAmou ntCur2|Currencies_Id|Currencies_Id_1|Currencies_Id _2|DifferentialCur1|DifferentialCur2|StartDate|End Date|Rate|FloatingRates_Id|AdditiveMargin|Multiply Margin|Principal|Strike|PaymentDate|Pairs_Id|Index ation|Quantity|Price|ThirdParty_Id_Cpty|DealType|D ealSubType|ClientMargin|OptionType|EventCustomType _Id|EventCfg_Id|VersionNumber|ActionDate|OriginalE ventDate|ActionDetail|Event_Id_Previous|Co
l
latAgrt_Id|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X| X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X| X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X| X|X|X|X||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||50|||||||||||||||| |||||||||||||||||||||||||||||||||||||||||||||||||| |||||||1|13|0|6|2|3|4|5|7|8|9|10|11|12|14|15|16|17 |18|19|20|21|22|23|24|25|26|27|28|29|30|31|32|33|3 4|35|36|37|38|39|40|41|42|43|44|45|46|47|48|49|50| 51|52|53|54|55|56|57|58|59|60|61|62|63|64|65|66|67 |68|69|70|71|72||A|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X |X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X |X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X|X |X|X|X|X|X|X|X|"

As you can see, there is alot of data in one particular field and it is not
typed manually as it is extracted from the database.

I would like to know if there is a coding that allows me to be able to
separate this value in the cell into different rows separated by the " | "
sign and to paste only numeric values on the next sheet.

Would be really great if you can help me out!

You have over 400 fields in that record, so you won't be able to use the Text
to Columns wizard unless you are using Excel 2007+

So the best solution would be a macro.

From reading your specifications, it appears as if you first want to split the
record into pipe-delimited fields, but have these listed below the original
record in rows.

And you also want to have the numeric data listed on a separate worksheet.

Here is one way of doing it; you can modify this according to your precise
needs. Of note, my test looks only at the first character of a field to
determine if it is numeric or not; you may want to use a different test as I am
not certain how you would classify field one.

You should also consider clearing out the destination areas before pasting, but
I didn't know how large that should be.

===============================================
Option Explicit
Sub GetNumericData()
Dim wSrc As Worksheet, wDest As Worksheet
Dim rSrc As Range, rDest As Range
Dim c As Range
Dim aSplit() As String
Dim i As Long, j As Long

Set wSrc = Worksheets("Sheet1")
Set wDest = Worksheets("Sheet2")

Set rSrc = wSrc.Range("A1") 'or expand as necessary
Set rDest = wDest.Range("A1")

aSplit = Split(rSrc.Value, "|")

For i = 0 To UBound(aSplit)
rSrc.Offset(i + 1, 0).Value = aSplit(i) 'split into rows

'put numeric data on next sheet
If Left(aSplit(i), 1) Like "#" Then
rDest.Offset(j, 0).Value = aSplit(i)
j = j + 1
End If
Next i
End Sub
===================================
--ron