Parsing out a string of text and numbers out of a larger string using vba

[Excel Newbie7]

Hi,

I was wondering if there was a way to parse out the SMDUResponseCode">bce52df15feb4c1a891b0ec766c078be< out of the following string of text. I tried using text to columns and splitting it using the left right mid text cutter and it doesn't seem to help. I have to find this set of string for multiple accounts. Keep in mind that the example below is only a small portion of the actual xml string that I search through. The smdu string doesn't always stay in the same spot as well. It could be in the beginning or end. I tried using a find function but it doesn't help.

If anyone know of a way it would be really helpful thanks,

<xmlDocument><element name="LossMitigationSupervisor">PARKSA1</element><element name="UserInfo">335 - emason</element><element name="LossMitigationSpecialist">HAMILTA</element><element name="AmortizationTermatOrigination">360</element><element name="ARMResetDate">2034-04-01</element><element name="AssetType401k">0</element><element name="AssetTypeBoatsorRVs">0</element><element name="AssetTypeChecking">0</element><element name="CurrentMarketValue"></element><element name="AssetTypeIRA">0</element><element name="AssetTypeOther">0</element><element name="AssetTypeOtherMortgage">0</element><element name="AssetTypeOtherRealEstateNonProperty">0</element><element name="AssetTypeSavings">0</element><element name="AssetTypeStocksBonds">0</element><element name="AssociationDues/FeesBeforeModification"></element><element name="AssociationDues/FeesPre-WorkoutAmount"></element><element name="AttorneyFees/Costs"></element><element name="BorrowerContributionAmount">.00</element><element name="BorrowerOccupancyIndicator"></element><element name="BorrowerTotalMonthlyObligations">502.26</element><element name="ContractualPaymentAmount">502.26</element><element name="CreditScore"></element><element name="CurrentLastPaidInstallmentDate"><element name="SMDUResponseCode">bce52df15feb4c1a891b0ec766c078be</element><xmlDocument><element name="LossMitigationSupervisor">PARKSA1</element><element name="UserInfo">335 - emason</element><element name="LossMitigationSpecialist">HAMILTA</element><element name="AmortizationTermatOrigination">360</element><element name="ARMResetDate">2034-04-01</element><element name="AssetType401k">0</element><element name="AssetTypeBoatsorRVs">0</element><element name="AssetTypeChecking">0</element><element name="CurrentMarketValue"></element><element name="AssetTypeIRA">0</element><element name="AssetTypeOther">0</element><element name="AssetTypeOtherMortgage">0</element><element name="AssetTypeOtherRealEstateNonProperty">0</element><element name="AssetTypeSavings">0</element><element name="AssetTypeStocksBonds">0</element><element name="AssociationDues/FeesBeforeModification"></element><element name="AssociationDues/FeesPre-WorkoutAmount"></element><element name="AttorneyFees/Costs"></element><element name="BorrowerContributionAmount">.00</element><element name="BorrowerOccupancyIndicator"></element><element name="BorrowerTotalMonthlyObligations">502.26</element><element name="ContractualPaymentAmount">502.26</element><element name="CreditScore"></element><element name="CurrentLastPaidInstallmentDate">

 

[Rick Rothstein]

Hi,

I was wondering if there was a way to parse out the SMDUResponseCode">bce52df15feb4c1a891b0ec766c078be< out of the following string of text. I tried using text to columns and splitting it using the left right mid text cutter and it doesn't seem to help. I have to find this set of string for multiple accounts. Keep in mind that the example below is only a small portion of the actual xml string that I search through. The smdu string doesn't always stay in the same spot as well. It could be in the beginning or end. I tried using a find function but it doesn't help.

If anyone know of a way it would be really helpful thanks,

<xmldocument><element name="LossMitigationSupervisor">PARKSA1</element><element name="UserInfo">335 - emason</element><element name="LossMitigationSpecialist">HAMILTA</element><element name="AmortizationTermatOrigination">360</element><element name="ARMResetDate">2034-04-01</element><element name="AssetType401k">0</element><element name="AssetTypeBoatsorRVs">0</element><element name="AssetTypeChecking">0</element><element name="CurrentMarketValue"></element><element name="AssetTypeIRA">0</element><element name="AssetTypeOther">0</element><element name="AssetTypeOtherMortgage">0</element><element name="AssetTypeOtherRealEstateNonProperty">0</element><element name="AssetTypeSavings">0</element><element name="AssetTypeStocksBonds">0</element><element name="AssociationDues/FeesBeforeModification"></element><element name="AssociationDues/FeesPre-WorkoutAmount"></element><element name="AttorneyFees/Costs"></element><element name="BorrowerContributionAmount">.00</element><element name="BorrowerOccupancyIndicator"></element><element name="BorrowerTotalMonthlyObligations">502.26</element><element name="ContractualPaymentAmount">502.26</element><element name="CreditScore"></element><element name="CurrentLastPaidInstallmentDate"><element name="SMDUResponseCode">bce52df15feb4c1a891b0ec766c078be</element><xmldocument><element name="LossMitigationSupervisor">PARKSA1</element><element name="UserInfo">335 - emason</element><element name="LossMitigationSpecialist">HAMILTA</element><element name="AmortizationTermatOrigination">360</element><element name="ARMResetDate">2034-04-01</element><element name="AssetType401k">0</element><element name="AssetTypeBoatsorRVs">0</element><element name="AssetTypeChecking">0</element><element name="CurrentMarketValue"></element><element name="AssetTypeIRA">0</element><element name="AssetTypeOther">0</element><element name="AssetTypeOtherMortgage">0</element><element name="AssetTypeOtherRealEstateNonProperty">0</element><element name="AssetTypeSavings">0</element><element name="AssetTypeStocksBonds">0</element><element name="AssociationDues/FeesBeforeModification"></element><element name="AssociationDues/FeesPre-WorkoutAmount"></element><element name="AttorneyFees/Costs"></element><element name="BorrowerContributionAmount">.00</element><element name="BorrowerOccupancyIndicator"></element><element name="BorrowerTotalMonthlyObligations">502.26</element><element name="ContractualPaymentAmount">502.26</element><element name="CreditScore"></element><element name="CurrentLastPaidInstallmentDate">

You need to tell us more about the text you want to pull out of that larger text string. For example, does it always begin with "bce" and end with "be"? Is it always preceded by "PARKSA1<element name="UserInfo">335 - emason</element><element name="LossMitigationSpecialist">HAMILTA</element><element name="AmortizationTermatOrigination">360</element><element name="ARMResetDate">2034-04-01</element><element name="AssetType401k">0</element><element name="AssetTypeBoatsorRVs">0</element><element name="AssetTypeChecking">0</element><element name="CurrentMarketValue"></element><element name="AssetTypeIRA">0</element><element name="AssetTypeOther">0</element><element name="AssetTypeOtherMortgage">0</element><element name="AssetTypeOtherRealEstateNonProperty">0</element><element name="AssetTypeSavings">0</element><element name="AssetTypeStocksBonds">0</element><element name="AssociationDues/FeesBeforeModification"></element><element name="AssociationDues/FeesPre-WorkoutAmount"></element><element name="AttorneyFees/Costs"></element><element name="BorrowerContributionAmount">.00</element><element name="BorrowerOccupancyIndicator"></element><element name="BorrowerTotalMonthlyObligations">502.26</element><element name="ContractualPaymentAmount">502.26</element>"? Does it always start at the 64th character position. Is it always 32 characters long? Does the text that follows it always the same as the first 10 characters of the text string? What I am looking for here is some guaranteed attribute that sets the text you want to retrieve apart from all the surrounding characters.</element></xmldocument></element></xmldocument>

 

[Excel Newbie7]

The text string is actually an xml and it doesn't paste into the forums correctly.
The start of the string always start with. (The less than sign goes here)xmlDocument(The greater sign goes here)(The less than sign goes here)element name "
The end of the string is always. (The less than sign goes here)element(The greater sign goes here)(The less than sign goes here)/xmlDocument(The greater sign goes here)

In between the start and finish there is always different data, but there is always this section that I'm looking for which is. (The less than sign goes here)element name(The greater sign goes here)
="SMDUResponseCode"32 character long text in here(The less than sign goes here)/element(The greater sign goes here)

The 32 character long text is what I'm looking for to parse out.

 

[Excel Newbie7]

I don't know why its not showing the things I'm typing in.

 

[Excel Newbie7]

I changed it so it will show. I didn't know these forums you cannot put in less than or greater sign in the messages.

 

[Rick Rothstein]

I changed it so it will show. I didn't know these forums you cannot put in less than or greater sign in the messages.

So you want the text between the less than and greater than signs, correct? If so, try this...

=MID(LEFT(A1,FIND(">",A1&">")-1),FIND("<",A1&"<")+1,32)